Tag Info

Hot answers tagged

4

I found the solution here: http://postgis.net/docs/UpdateGeometrySRID.html --This will change the srid of the roads table to 3857 from whatever it was before UpdateGeometrySRID(varchar table_name, varchar column_name, integer srid); So for my database the table name was 'planet_osm_line' and the column that contains the srid and geometry is 'way'. the ...


3

You need ST_Difference which returns the the geometry of a feature that does not intersect with another feature. In your case something like this: SELECT l.id, ST_Difference(l.geom, p.wkb_geometry) As diff_geom FROM lines l, polygons p Modify for your actual table and field names.


2

Do not use the regular "Add PostGIS Layers", it will not show your raster table. The plugin "Load Postgis Raster to QGIS" was not updated to QGIS v.2.0, so it won't work either. In QGIS v.2.6 go to menu Database->DB Manager->DB Manager, set the database connection and you will see the raster table. You can drag and drop it or right click and select Add to ...


2

The answer of @underdark was close, but does not work, because the destination ID's must be passed as an array. Here is the same example of the workshop using queries instead of passing the ID's directly. SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost( 'SELECT id, source, target, cost FROM edge_table WHERE cost >= 0', ...


1

You could try something like this SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost( 'SELECT id, source, target, cost FROM edge_table WHERE cost >= 0', (select nodeid FROM origins), (select nodeid FROM destinations), false, false) not tested, might be with or without brackets.


1

If you are using Django (GeoDjango), look at vectorformats, which will produce GeoJSON that you can use directly in Google Maps -- we are using this in production, on a decent sized database, millions or rows and complex polygons, and it works really well. If you want to query Postgis directly, use ST_AsGeoJSON as @underdark says, but note, you need to do a ...


1

Don't code, use ST_AsGeoJSON — Return the geometry as a GeoJSON element.


1

You're using an outdated tutorial with the new pgRouting 2.0 release, I guess. As the error states, it cannot find the function, because the function has been renamed. I recommend you to look at the pgRouting Workshop. The pgRouting documentation also gives brief examples for every function.


1

Maybe try something like this? http://docs.geotools.org/stable/userguide/library/jts/geometry.html#creating-circularstring


1

Use STDWithin to check if the features are within the desired distance For geography units are in meters and measurement is defaulted to use_spheroid=true The documentation page provides simple examples of the usage of this function. In your case this should work: SELECT key FROM the_table WHERE ST_DWithin( GEOM, ...


1

There are a series of projections suited to this exact situation, 10TM, 6TM and 3TM. They are essentially Transverse Mercator slices with custom central meridians, spanning 10 degrees, 6 degrees, or 3 degrees of longitude, respectively. I first encountered them working on data within the City of Calgary, which straddles two UTM zones. A reference: ...


1

I finally figured it out: The real problem was that I stored the coordinates with latitude as the first value. That is the way Google Earth or Google Maps need the values, but it is WRONG for PostGIS. The coordinates need to be stored as (lon,lat). The way I did it, distances were calculated for the sea near Somalia. :P Now the distance calculations ...


1

Apparently, I don't have enough points to add a comment so I am using this Answer just to say that I tried both ST_MakeEnvelope vs the maths compare of "x > min_x and x < max_x and y > min_y and y < max_y" ...on average ST_MakeEnvelope took 60ms and maths compare took 155ms on my particular bbox query. So the spatial search ST_MakeEnvelope should be ...


1

Given Paul Ramsey's excellent explanation of why the next question is what can be done about it. How do you SELECT DISTINCT on geometry fields and have it perform as expected? In Paul's answer, I proposed using SELECT MAX(geom) FROM the_table GROUP BY ST_AsBinary(geom); but MAX() is slow, apparently requiring a table scan. Instead, I found this to be ...



Only top voted, non community-wiki answers of a minimum length are eligible