If you do a lot of web-based GIS applications, a common desire is to allow a user to
draw out an area on the map and then do searches against that area and return back a FeatureCollection
where each feature is composed of a geometry and attributes about that feature. In the past the format
was GML or KML, but the world seems to be moving to prefer JSON/GeoJSON. Normally you'd throw
a mapping server that talks Web Feature Service
, do more or less with a webscripting glue, or use a Webservice
such as CartoDb that lets you pass along raw SQL.
In this article we'll demonstrate how to build GeoJSON feature collections that can be consumed by web mapping apps.
using
the built in JSON functions in PostgreSQL 9.2 and some PostGIS hugging.
Even if you
don't use PostGIS, we hope you'll come away with some techniques for working with
PostgreSQL extended types and also how to morph relational data into JSON buckets.
Outputting GeoJSON Feature Collections
We want the result of all our searches to output as GeoJSON feature collections which look something like
the below (partially clip from GeoJSON spec
{ "type": "FeatureCollection",
"features": [
{ "type": "Feature",
"geometry": {"type": "Point", "coordinates": [102.0, 0.5]},
"properties": {"prop0": "value0"}
},
{ "type": "Feature",
"geometry": {
"type": "LineString",
"coordinates": [
[102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0]
]
},
"properties": {
"prop0": "value0",
"prop1": 0.0
}
}
]
}
As you can see it's not the nice flat tabular looking thing we relational database
folks have grown to love. It's got a few curves here and there and the geometry column is output
separate from the other fun loving attributes. This is different from what we showed in
PostgreSQL 9.2 Native JSON type support.
While you need PLV8JS to consume something like this, you can generate something like this
with barebones PostgreSQL JSON support. So how do you do that?
Setup our test data
We'll test using this table:
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)') );
Query to output as FeatureCollection
To output as a feature collection, we can do this:
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;
or avoiding a self-join by doing this
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((SELECT l FROM (SELECT loc_id, loc_name) As l
)) As properties
FROM locations As lg ) As f ) As fc;
Both above queries output the below, which we've reformatted to fit better on the page
{"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"}}
]
}
Now you may be wondering why we need a self join or nested subselect. Although PostgreSQL 9.2 is smarter now about
inferring column names in a subquery, thanks to Andrew Dunstan, Tom Lane, and others, it still is not capable of allowing you to define a row object with nice column
names without casting to a defined type. So if you did the shorter:
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((loc_id, loc_name)) As properties
FROM locations As lg ) As f ) As fc;
You get stuck with f1,f2...fn for column names as shown here:
{"type":"FeatureCollection",
"features":[
{"type":"Feature","geometry":{"type":"Point","coordinates":[42.400469999999999,-71.2577]},
"properties":{"f1":1,"f2":"Waltham, MA"}},
{"type":"Feature","geometry":{"type":"Point","coordinates":[42.990189999999998,-71.462590000000006]},
"properties":{"f1":2,"f2":"Manchester, NH"}},
{"type":"Feature","geometry":{"type":"Point","coordinates":[-96.757239999999996,32.909770000000002]},
"properties":{"f1":3,"f2":"TI Blvd, TX"}}]
}
Tracked: Sep 23, 21:42