Tag Info

New answers tagged

0

It looks like the ability to create rasters is not yet available in the driver. However, you can create a layer using your standard PostgreSQL Python driver (i.e. psycopg2) and then open it from GDAL. So you'd create a layer using ST_MakeEmptyRaster and ST_AddBand: CREATE TABLE rtest (gid serial primary key, rast raster); INSERT INTO rtest (rast) VALUES ...


1

You could use the Java implementation of GeographicLib to solve the inverse geodesic problem on WGS84. import net.sf.geographiclib.*; ... private double calculateDistance( double lon1, double lat1, double lon2, double lat2) { GeodesicData g = Geodesic.WGS84.Inverse(lat1, lon1, lat2, lon2); return g.s12; // distance in ...


0

It is not a good decission, tables should represent static entities inside your application that will remain the same across different environments (Think local, staging, development, production). Like HeyOverThere points out, what you need is a dynamic schema. I implemented a similar system, a user can create multiple layers so I have two entities: ...


1

Pros: It's a high performance and flexible key/value that can be indexed and queried. I have a complex schemaless web GIS that uses an HStore column for storing the features properties. Cons: Most WMS/WFS tools like Mapserver, Geoserver and similars can't understand and work with HStore columns, so you end up creating hard coded SQL Views (In Geoserver ...


3

The short answer is you can't do that unless your points are very close together and you want the answer in degrees. JTS knows nothing about units or the curvature of the earth. So you need to pull in some GeoTools jars that do know about such things. Then you can create a method like: private void calculateDistance( ...


1

I think you could use the following code to obtain source information for your layer. Select a layer from the layer window and input the code into the Python Console: layer = qgis.utils.iface.activeLayer() print layer.source() You should receive information about the layer. Hope this helps!


1

I assume it's running into trouble when you try to route from one part on the network to another part which is disconnected due to closure (e.g. when link 58-47 is removed). Instead of removing these links, try to put their costs really high. That way, you will be able to identify those links which get disconnected by their high cost sums.


0

Mapbox has a plugin for mapbox.js to help snap and clean up GPS traces. https://github.com/mapbox/mapbox-match.js.


3

One of the best place to search and explore source code for PostGIS is through the GitHub mirror: https://github.com/postgis/postgis For instance, search ST_DWithin, where you will then search LWGEOM_dwithin, then lwgeom_mindistance2d_tolerance, then lw_dist2d_comp, then lw_dist2d_recursive, and then different versions of lw_dist2d_distribute* ...


3

You can bind a trigger function to as many tables as you want; just execute a CREATE TRIGGER statement for each binding. Make sure to schema-qualify the table name in your statement (BEFORE INSERT OR UPDATE ON a.point etc.) If you have a large number of schemas, you could generate the SQL dynamically by iterating over the rows in ...


2

SELECT usdma.geom FROM usdma, usstates WHERE ST_Overlaps(usdma.geom, usstates.geom) AND usstates.name = 'TX'; I'd rather go for ST_Intersects or ST_Within though.


1

You can get the shortest route between two points using PostGIS with ST_Distance (if you want to know the distance) or with ST_MakeLine (for the geometry): http://postgis.net/docs/ST_Distance.html http://postgis.net/docs/ST_MakeLine.html If you want to use the network for routing, then you need a network topology. You could try something like pggraph, ...


1

SELECT category, ST_Union(geom) geom FROM s.road GROUP BY category; You might want to use ST_Dump() afterwards not to have multilinestrings in the result.


1

How big is your dataset? You could create a fishnet grid in postgis See here Then you could interpolate your raster with your grid with various tools (gdal/qgis): see here Dont forget to index your grid to optimize speed. Good luck


2

I could reproduce thew issue with your shapefile data. I used ogr2ogr for converting the shapefiles into PostGIS tables and your query finds nothing. Select * from state where ST_Contains(geom, (select geom from region where REGION_N='CAPITAL_REGION')); Next I opened your shapefiles with OpenJUMP and made some tests. Both layers have some topology errors ...


2

No space should be (also float8 is the same as double precision and a bit faster to type) UPDATE main_table SET geom = ST_SetSRID( ST_MakePoint(lng::float8, lat::float8) ,4326);


1

I was trying to do some changes and this looks like solution since everything works. I deleted old database and again created new one by creating a spatial database directly in pgAdmin: Created new database (name,owner...) Opened the query dialog box and wrote CREATE EXTENSION postgis; and then SELECT postgis_full_version(); This step ...


2

Taking as hypothesis that: city.geom is in EPSG:4326 lat, lng in pets table can also be taken as EPSG:4326 That you want to sum the number of cats and dogs that are in each geometry of the table city That the geoms in table city do not overlap or if overlaps you don't care to count two times the pets city has a pk called gid You are not interested in the ...


0

In my case, the answer was really simple: I needed to make the create extension and create table before the set seach_path to inserts This will not work: SET search_path = myschema, pg_catalog; CREATE EXTENSION IF NOT EXISTS postgis; CREATE EXTENSION IF NOT EXISTS postgis_topology; CREATE EXTENSION IF NOT EXISTS adminpack; CREATE TABLE IF NOT EXISTS ...


0

A self-join allows you to operate on the relationship between pairs of two features. But I don't think you're interested in pairs: for each feature, you want to operate on the relationship between that feature and all other features in your dataset. You can accomplish this with a subquery expression: CREATE TABLE parcels_trimmed AS SELECT id, ...


1

As far as I checked in code, the feature id (FID) should be put into SVG, but that possiblity is commented out :/ https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/java/org/geoserver/wms/svg/SVGWriter.java private void setUpWriterHandler(SimpleFeatureType featureType, boolean doCollect) throws IOException { if (doCollect) { ...


0

I would try something like this assuming we're interested only in those geometries that lie inside of some other geometry - these are considered as higher valued. UPDATE polygons -- adjust geometry SET wkb_geometry = new_polygon FROM ( SELECT p1.id, -- take the difference of the geometries ST_Difference(p1.wkb_geometry, ...


0

@jmsmcb answered this on Twitter. https://twitter.com/jmsmcb/status/624231773107564545 SELECT *, ST_Distance(the_geom, (SELECT the_geom FROM polygon_near)) as distance_to_polygon from points_near I think this returned degrees, so asking how to get meters or km. And here it is: SELECT *, ST_Distance(the_geom::geography, (SELECT the_geom FROM ...


3

As the Douglas Peucker algorithm works by iteratively removing those points that are within a given tolerance from some line between two other points in some given input unit, you can assume that if you have a geometry covering a large north-south distance, you will get greater amounts of simplification for a given tolerance in degrees as you move further ...


0

I started to dive into VTK - The Visualization ToolKit (VTK) is an open source, freely available software system for 3D computer graphics. It is pretty powerful and robust tool, seems to be able to do all the operations I asked for though I can't confirm it all yet. On the other hand it doesn't support datum and projection as is expected from GIS software. ...


1

Create Table p( id serial primary key, geom geometry(point,2100) ); The table has ~250k Points, and with the following query you can find the closest point in the same dataset (that is not the same point). select a.id ,b.id target ,b.d distance from p as a cross join lateral (select distinct on (a.id) b.id ,a.geom <-> b.geom d from p as ...


0

After some tinkering I realized that I had to encode the bbox in an ST_MakeEnvelope call: postgis.org/docs/ST_MakeEnvelope.html. This is because the call to mercator simply returns a conversion from the point being represented to the [minLng, minLat, maxLng, maxLat]. I can use ST_MakeEnvelope to return the proper PostGIS type based on this input. Code is as ...


0

OrbisGIS 5.0 is an open source GUI tool specialized at database view/processing. The built-in SQL console editor have syntax highlighting, code completion and sql functions search engine. In the geocatalog using drag&drop you can transfer shape files to the database. The table editor has been specifically designed for viewing/editing large database ...


0

If someone encounters the same problem, I wrote a function that solved it CREATE OR REPLACE FUNCTION homeflip() RETURNS integer AS $func$ DECLARE area_cursor CURSOR IS select * from your_table; area_geom geometry(Polygon,4326); nbLines integer := 0; BEGIN RAISE NOTICE 'START homeflip'; FOR area_var IN area_cursor LOOP area_geom := ...


3

To add a column to an existing table, use the ALTER TABLE DDL, e.g.: ALTER TABLE my_table ADD COLUMN the_geom_mercator geometry(Geometry,3857); which can be populated from another column (the_geom) using: UPDATE my_table SET the_geom_mercator = ST_Transform(the_geom, 3857) FROM spatial_ref_sys WHERE ST_SRID(the_geom) = srid; (the second line FROM ...


2

var point = 'ST_SetSRID(ST_MakePoint(' + req.params.x + ',' + req.params.y + '),4326)'; var test_query = 'SELECT st_asgeojson(wkb_geometry) AS feature ' + 'FROM ogrgeojson WHERE ' + 'ST_INTERSECTS(wkb_geometry, ' + point + ')';


1

Did you read http://postgis.net/docs/ST_Contains.html? ST_Contains — Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A. Creating SQL queries which construct geometry from WKT is a good and controlled way for testing the syntax select ST_Contains( ...


0

Only way I've been able to do it (I think it works for osm2pgrouting, but haven't tried recently) Is to first change the search_path of your database temporarily so preferred schema is first: ALTER DATABASE yourdb SET search_path = osm_data, public; Then run osm2pgrouting Alternatively you could create a user specifically for loading and then set the ...


1

You can create an unconstrained SRID geometry column to hold the native form and then transform to existing. Here is a contrived example assuming you have polygons that you are copying from a staging table (if you have mixed, you can set type to geometry e.g geometry(Geometry,3857): CREATE TABLE poi(gid serial primary key, geom_native ...


0

Turns out the 'clean' option does the trick, as per the osm2pgrouting documentation waaaay at the bottom: all available parameters are: required: -file -- name of your osm xml file -dbname -- name of your database -user -- name of the user, which have write access to the database -conf -- name of your configuration xml file optional: -host -- ...


1

shp2pgsql translates a shapefile and its attribute table to sql commands. It does all the heavy lifting for you so the result should be workable as it's being produced, and it's a starting point for you to optimize if you want by: 1) Create spatial index (- I flag in shp2pgsql). You can create a spatial index in an already created table by create index ...


1

Actually, the manual is incorrect. If the SRID is unknown, it is assumed to be WGS84. For PostGIS 2.0 and later, the radius of the spheroid is obtained from the SRID. Let's add a Martian projection system for fun: SELECT Postgis_version() INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) VALUES (49900, 'IAU2000', 49900, ...


8

You can use QGIS' quadrant placement specifier determined from the line's azimuth to place a better label. The quadrant specifies 8 positions around a point: [ 0=Above Left | 1=Above | 2=Above Right | 3=Left | 4=Over | 5=Right | 6=Below Left | 7=Below | 8=Below Right ] Here's an example around Null Island, creating a table and two views. ...


1

okay.. as it's in map units this should be fairly straight-forward, within limitations. You already know the height of the label. If it was in points it would be scale-dependant. This assumes a fixed label size, so how well this works depends on how uniform your labels are, and whether or not you use a proportional or fixed-width font (fixed width is easier ...


3

One approach would be to use a CTE and use ST_DWithin, which will efficiently use a spatial index to get all addresses that are within 700 meters of the coastlines and then a second query based on those results to only choose those that have a minimum distance of at least 600 meters. You can't use min(ST_Distance....) in a WHERE clause, so this was the most ...


1

I'm making the assumption that you have multiple records in your coastline table. That could cause these "false positives" if there is piece of coastline further than the nearest bit that still meets your join criteria. The following query merges the coastline into one record, then joins on that. The INNER JOIN will work, you won't get duplicates (because ...


5

It doesn't use WGS80. You are looking at old documentation for version 1.4. In more up to date documentation it states WGS84 is assumed. See here where it states: This function currently does not look at the SRID of a geometry and will always assume its in WGS 84 long lat. The version you quote from (1.4) also only implemented ST_Distance_Sphere ...


2

The postgis 2.1 manual states that it assumes WGS84. And uses a sphere to return a faster result than ST_Distance_Spheroid which returns the minimum distance between two lon/lat geometries given a particular spheroid.


1

The question is a bit old, but just to let you know that postGIS is now officialy supported by amazon RDS. That makes things very easy as explained here. See also this post.


0

For future reference in case anyone else has the same misfortune, I got the same error message trying to add a SQL Spatial table and found that you need to "Check" the name (ISqlWorkspace::CheckDatasetName) before using it. https://badarcgis.wordpress.com/2012/03/30/query-class-name-is-invalid/


3

Setting aside the GeoJSON serialization, the following takes about 6.3 seconds on my laptop: SELECT ST_MakePolygon( ST_ExteriorRing( (ST_Dump( ST_Union( ST_Buffer(geom, 20, 2)))).geom)) FROM bz_edges Looking at the data in OpenJUMP, I noticed quite a bit of detail in the street segments, relative to the desired level of ...


0

I'm working on automating this guide so people won't have as much trouble, as I too struggled in some spots. Anyway your error is in this part of the guide: Double check and make sure this is all correct. vim osm-bright/osm-bright.osm2pgsql.mml Find the lines with URLs pointing to shapefiles (ending .zip) and replace each one with these appropriate ...


2

What's i30? If it's a table, you'll need something like: SELECT ST_Buffer(geog,10,'endcap=round join=round') FROM i30; and your result will have a buffered geography for each row in i30.


2

Perhaps there's a more elegant way, but the following query should work: with nearest_line as -- put the query to get the nearest line in a CTE -- use the CTE twice (select source, target from segment_table order by (ST_Distance_Sphere(ST_GeomFromText('POINT(lon lat)',4326) limit 1) select id from -- A union query to get the ...


1

Hope this works for anyone looking for something similar. This is just the tangent angle. Getting an actual line tangent with this angle is just a matter of using ST_Project(point_in_line, tangent_length, north_heading) or regular vector construction from an angle The inputs are the line you want to calculate the tangent from and a point to project into the ...



Top 50 recent answers are included