New answers tagged

2

In PostgreSQL 9.4+, you can use the json_build_object and jsonb_build_object functions to construct JSON without subqueries, which feels more similar to how this is done outside of PostgreSQL. For example: WITH test_geoms(id, point_type, geom) AS (VALUES(1, 'Park', ST_MakePoint(0,0)), (2, 'School', ST_MakePoint(1,1))) SELECT json_build_object( 'type',...


3

You can create a feature collection by using row_to_json, selecting 'FeatureCollection' as type, and rolling up all you geometries into an array using array_to_json(array_agg(geom)) in a subquery, which creates the appropriate GeoJSON using ST_AsGeoJSON. You can also add the EPSG:4326 as a crs attribute, at the top level of the GeoJSON. When you are ...


0

A geometry is returned as part of the geocode() result, by which you can use to locate the nearest ID from any table that has a geometry column. select gid from tiger.edges order by st_setsrid(the_geom,4326) <-> ST_GeomFromText('POINT(-71.06941 42.34225)',4326) limit 1;


0

Regarding the warnings, I received the same warnings. Regarding the geocode time of 136 seconds, I have 37 states loaded and your request takes 2.8 seconds on my windows 64 bit box. If you have more states loaded, it will take longer. If you have fewer states loaded than I, then you may need to double check your indexes and modify your PostgreSQL server ...


0

It's tricky to use ST_Within if the boundaries are not exactly shared. A workaround is to take a point inside your districts with ST_PointOnSurface and see if this point is inside a county. Your query should look like: select nazorp from kraje as k, orp_wgs as o WHERE ST_Within(ST_PointOnSurface(o.geom),k.geom) AND k.nazev = 'Libereck√Ĺ'


0

It sounds like you need a solution that is robust to alignment issues. Try SELECT nazorp from kraje as k, orp_wgs as o WHERE ST_Intersects(o.geom,k.geom) AND NOT ST_Touches(o.geom,k.geom) AND k.nazev = 'Libereck√Ĺ' NOT ST_Touches() will exclude districts that only share a boundary, and don't have any overlap. If this still returns some unwanted ...



Top 50 recent answers are included