Importing GeoJSON

Goal

We sometimes need to import a "shapefile" in GeoJSON format to create a table containing geometric shapes. The idea is to import the GeoJSON file into a table that has one row per feature (i.e., shape), along with possible meta data about the shape, then convert the GeoJSON shape column into a Postgres GIS geometry data type that can be consumed by PostGres queries. The initial example of this is importing Los Angeles City Neighborhood Council boundary data into a neighborhood table that can be used by the application.

Steps

1. Find a source of GeoJSON data. For the LA Neighborhood Council Boundaries, we can download the data from the Los Angeles GeoHub Just choose the GeoJSON Download button. Open the downloaded file with a text editor and edit it to get it in a newline-delimited list of JSON-formatted objects corresponding to the rows you want in the database. For this case, each object contains JSON like this:

{ "type": "Feature", 
"properties": { 
  "FID": 1, 
  "Name": "Arleta", 
  "FolderPath": "2018 Maps/2018 Bylaw Amendments/Boundary Adjustments", 
  "SymbolID": 0, 
  "AltMode": 0, 
  "Base": 0, 
  "Clamped": -1, 
  "Extruded": 0, 
  "PopupInfo": "https://empowerla.org/anc/",
  "Shape_Leng": 0.13244134512, "Shape_Area": 0.00083029456976500002, 
  "Shape__Area": 12446361.1289063, 
  "Shape__Length": 16399.3729303324, 
  "GlobalID": "a2b9ce7e-4571-49d6-8c8f-4c63604f7e7d" }, 
  "geometry": { 
    "type": "Polygon", 
    "coordinates": [ [ [ -118.4500639, 34.24992, 0.0 ], [ -118.4505666, 34.2506395, 0.0 ], 
    [ -118.4515978, 34.2516516, 0.0 ], [ -118.4475795, 34.257325, 0.0 ], 
    [ -118.4474992, 34.257608500000103, 0.0 ], [ -118.4453844, 34.260165400000098, 0.0 ],
    [ -118.4436421, 34.2628372, 0.0 ], [ -118.4432401, 34.262740300000097, 0.0 ], 
    [ -118.4429667, 34.2635833, 0.0 ], [ -118.4420859, 34.264727, 0.0 ], 
    [ -118.4409931, 34.262977200000101, 0.0 ], [ -118.440039, 34.2615881, 0.0 ], 
    [ -118.4388811, 34.2602965, 0.0 ], [ -118.4160948, 34.2388043, 0.0 ], [ -118.413213, 34.24091, 0.0 ], 
    [ -118.4071871, 34.2352264, 0.0 ], [ -118.4195749, 34.2261814, 0.0 ], 
    [ -118.4207477, 34.225263600000098, 0.0 ], [ -118.42147, 34.2244522, 0.0 ], 
    [ -118.4220346, 34.2233795000001, 0.0 ], [ -118.4226059, 34.2216777, 0.0 ], 
    [ -118.4327344, 34.221683100000099, 0.0 ], [ -118.4327588, 34.225481300000098, 0.0 ], 
    [ -118.432829, 34.226048300000102, 0.0 ], [ -118.4330199, 34.2265167, 0.0 ], 
    [ -118.4333019, 34.2269061000001, 0.0 ], [ -118.4361349, 34.229594200000101, 0.0 ], 
    [ -118.44118, 34.236365, 0.0 ], [ -118.4456323, 34.2431287, 0.0 ], [ -118.447789, 34.2464867, 0.0 ], 
    [ -118.4500639, 34.24992, 0.0 ] ] ] 
  } 
}

Then save the result to a file in a directory where you can access it from your terminal.

2. Open a psql connection to the database where you want to import the data. For example

psql -h localhost -U postgres -d food -p 5434 

(Then enter the password pgpass when prompted) to open a connection to a local postgres instance.

3. At the psql prompt, create a table called temp with a single column named data with data type jsonb:

food=# create table neighborhood_load(data jsonb);
CREATE TABLE
food=#

4. Copy the data from the table you created earlier into the new table:

food=# \COPY neighborhood_load(data) FROM ncdata.geojson;
COPY 99
food=#

The returned number will indicate how many rows were imported.

5. Close psql. At this point, you have a table called neighborhood_load with a single column named data that contains one JSON object per row.

6. Now you can create a table with the desired schema and write a query to populate the table from the neighborhood_load table. See this db migration script for an example. The key query is an INSERT/SELECT that uses postgresql syntax to extract the relevant properties from the data columm:

INSERT INTO neighborhood (id, name, empower_link, geometry_text, geometry)
SELECT 
CAST (data->'properties'->>'FID' AS integer),
data->'properties'->>'Name',  
data->'properties'->>'PopupInfo', 
data->>'geometry',
ST_GeomFromGeoJSON(data->>'geometry') 
FROM neighborhood_load;

I chose to keep the human-readable JSON text formatted geometry in a geometry_text column, but also convert the geometry to a PostgreSql geometry data type in the geometry column so PostgreSql can do queries and calculations on the shape. Alternatively, you could use a geography data type, which accounts for the Earth's curvature, if you need the additional accuracy provided over large distances.

7. You can then delete the temporary loading table if desired.