Constructing GeoJSON in PostgreSQL#

In order to efficiently generate a GeoJSON representation of a vast number of locations, I’m currently experimenting with generating the GeoJSON directly inside a PostgreSQL SQL query using json_build_object() and friends.

Here’s my query so far, which illustrates various new patterns I’ve been learning - including a join against a CTE that extracts a many-to-many table into a format that can be represented in the final output as a JSON array.

1
with location_concordances as (
2
  select location.id, coalesce(
3
    jsonb_agg(ci.authority || ':' || ci.identifier) filter (
4
      where ci.authority IS NOT NULL
5
    ),
6
    '[]'
7
  ) as concordances
8
  from location
9
  left join concordance_location cl on location.id = cl.location_id
10
  left join concordance_identifier ci on ci.id = cl.concordanceidentifier_id
11
  group by location.id
12
)
13
SELECT
14
  json_build_object(
15
    'type', 'Feature',
16
    'properties', json_build_object(
17
      'id',
18
      location.public_id,
19
      'name',
20
      location.name,
21
      'state', state.abbreviation,
22
      'latitude', location.latitude,
23
      'longitude', location.longitude,
24
      'location_type', location_type.name,
25
      'import_ref', location.import_ref,
26
      'phone_number', location.phone_number,
27
      'full_address', location.full_address,
28
      'city', location.city,
29
      'county', county.name,
30
      'google_places_id', location.google_places_id,
31
      'vaccinefinder_location_id', location.vaccinefinder_location_id,
32
      'vaccinespotter_location_id', location.vaccinespotter_location_id,
33
      'zip_code', location.zip_code,
34
      'hours', location.hours,
35
      'website', location.website,
36
      'preferred_contact_method', location.preferred_contact_method,
37
      'provider', case
38
        when provider.name is null then null
39
        else jsonb_build_object('name', provider.name, 'type', provider_type.name)
40
      end,
41
      'concordances', location_concordances.concordances
42
    ),
43
    'geometry', json_build_object(
44
      'type', 'Point',
45
      'coordinates', json_build_array(location.longitude, location.latitude)
46
    )
47
  )
48
  from location
49
    join state on location.state_id = state.id
50
    join county on location.county_id = county.id
51
    join location_type on location.location_type_id = location_type.id
52
    join location_concordances on location.id = location_concordances.id
53
    left join provider on location.provider_id = provider.id
54
    left join provider_type on provider.provider_type_id = provider_type.id
55
limit 1

Example output from this query:

1
{
2
    "type": "Feature",
3
    "properties": {
4
        "id": "rec00NpJzUnVDpLaQ",
5
        "name": "Kaiser Permanente Pharmacy #568",
6
        "state": "CA",
7
        "latitude": 34.081292,
8
        "longitude": -117.996576,
9
        "location_type": "Pharmacy",
10
        "import_ref": "vca-airtable:rec00NpJzUnVDpLaQ",
11
        "phone_number": "833-480-4700",
12
        "full_address": "12761 Schabarum Ave Plaza Level RM 1100, Irwindale, CA 91706",
13
        "city": null,
14
        "county": "Los Angeles",
15
        "google_places_id": "ChIJizKuijvXwoAR4VAXM2Ek4Nc",
16
        "vaccinefinder_location_id": null,
17
        "vaccinespotter_location_id": null,
18
        "zip_code": null,
19
        "hours": "Monday - Friday: 8:00 AM – 5:00 PM\nSaturday - Sunday: Closed",
20
        "website": null,
21
        "preferred_contact_method": null,
22
        "provider": {
23
            "name": "Kaiser Permanente",
24
            "type": "Health Plan"
25
        },
26
        "concordances": [
27
            "google_places:ChIJizKuijvXwoAR4VAXM2Ek4Nc"
28
        ]
29
    },
30
    "geometry": {
31
        "type": "Point",
32
        "coordinates": [
33
            -117.996576,
34
            34.081292
35
        ]
36
    }
37
}

Using ST_AsGeoJSON#

After writing this TIL I found a much quicker option: the ST_AsGeoJSON() function. This works if your table has a geometry column on it - in my case I enhanced my location table to include a point column.

The following SQL generates a full GeoJSON feature row for each location in the database:

1
select ST_AsGeoJSON(location.*) from location

The location.* is required - if you try to use ST_AsGeoJSON(*) you get an error claiming that “No function matches the given name and argument types”.

The output looks something like this:

1
{
2
  "type": "Feature",
3
  "geometry": {
4
    "type": "Point",
5
    "coordinates": [
6
      -117.65499,
7
      34.10835
8
    ]
9
  },
10
  "properties": {
11
    "id": 4285,
12
    "name": "Vons Pharmacy #2681",
13
    "public_id": "recbECvQlveAyBorV",
14
    "import_json": {
15
      "Name": "Vons Pharmacy #2681",
16
      "Hours": "Monday - Friday: 8:00 AM \u2013 8:00 PM\nSaturday - Sunday: 9:00 AM \u2013 5:00 PM",
17
      "County": "San Bernardino County",
18
      "county_id": [
19
        "reclZ8DWOEuoluStG"
20
      ]
21
    }
22
  }
23
}

That import_json section is a PostgreSQL JSON column in the database - note that it gets output as nested data inside the "properties" object.