Visitas: 32

Asumimos que tienen configurado un base de datos en PostgreSQL con PostGIS. Ahora vamos a crear una tabla con tres datos. Escriban la siguiente sentencia sql:

CREATE TABLE locations(loc_id integer primary key
 , loc_name varchar(70), geog geography(POINT) );
 INSERT INTO locations(loc_id, loc_name, geog)
 VALUES (1, 'Waltham, MA', ST_GeogFromText('POINT(42.40047 -71.2577)') )
 , (2, 'Manchester, NH', ST_GeogFromText('POINT(42.99019 -71.46259)') )
 , (3, 'TI Blvd, TX', ST_GeogFromText('POINT(-96.75724 32.90977)') );

Ahora escriban esta sentencia sql para generar un formato GeoJSON:

SELECT row_to_json(fc)
 FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
 FROM (SELECT 'Feature' As type
 , ST_AsGeoJSON(lg.geog)::json As geometry
 , row_to_json(lp) As properties
 FROM locations As lg
 INNER JOIN (SELECT loc_id, loc_name FROM locations) As lp
 ON lg.loc_id = lp.loc_id ) As f ) As fc;

El resultado sería:

{"type":"FeatureCollection",
 "features":[
 {"type":"Feature","geometry":{"type":"Point","coordinates":[42.400469999999999,-71.2577]},
 "properties":{"loc_id":1,"loc_name":"Waltham, MA"}},
 {"type":"Feature","geometry":{"type":"Point","coordinates":[42.990189999999998,-71.462590000000006]},
 "properties":{"loc_id":2,"loc_name":"Manchester, NH"}},
 {"type":"Feature","geometry":{"type":"Point","coordinates":[-96.757239999999996,32.909770000000002]},
 "properties":{"loc_id":3,"loc_name":"TI Blvd, TX"}}
 ]
 }