Tiro monedas al aire, pero hasta ahora nunca las pude agarrar.

Asumimos que tienen configurado un base de datos en Postgres 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"}}
]
}

Comments

comments


Comments

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *