User Tools

Site Tools


postgis

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
Last revision Both sides next revision
postgis [2016/10/06 14:55]
mantis
postgis [2020/02/12 12:05]
mantis [Export table to geojson]
Line 99: Line 99:
 ===== Update gis table registry ===== ===== Update gis table registry =====
  
-In table spatial_ref_sys all tables containing PostGIS types are listed.+In table spatial_ref_sysall tables containing PostGIS types are listed.
 To refresh this cache: To refresh this cache:
  
Line 116: Line 116:
 UPDATE mytable SET the_geom = ST_SetSRID(ST_MakePoint(longitude,​latitude),​4326);​ UPDATE mytable SET the_geom = ST_SetSRID(ST_MakePoint(longitude,​latitude),​4326);​
 </​code>​ </​code>​
 +===== Export table to geojson =====
 +
 +Building a FeatureCollection ([[https://​postgis.net/​docs/​ST_AsGeoJSON.html|docs]],​ [[https://​gist.github.com/​brambow/​889aca48831e189a62eec5a70067bf8e|code snippet]]):
 +
 +<code psql>
 + ​SELECT json_build_object(
 +    '​type',​ '​FeatureCollection',​
 +    '​crs', ​ json_build_object(
 +        '​type', ​     '​name', ​
 +        '​properties',​ json_build_object(
 +            '​name',​ '​EPSG:​4326'  ​
 +        )
 +    ), 
 +    '​features',​ json_agg(
 +        json_build_object(
 +              '​type', ​      '​Feature',​
 +    '​id', ​        id,
 +    '​geometry', ​  ​ST_AsGeoJSON(the_geom)::​json,​
 +    '​properties',​ json_build_object(
 +        '​name',​ name,
 +        '​frc',​ frc,
 +        '​fromjunction',​ fromjunction,​
 +        '​tojunction',​ tojunction,
 +          )
 +       )
 +    )
 +)
 + FROM roadmap_table;​
 +</​code>​
 +
 +
 ===== Select all geometries in an area ===== ===== Select all geometries in an area =====
 <code sql> <code sql>
postgis.txt ยท Last modified: 2020/02/12 12:06 by mantis