GeoJSON to PostGIS SQL

What this tool does

It reads your features and writes SQL: optionally a CREATE TABLE whose columns come from the union of all property keys with types inferred from their values, a GIST index on the geometry column, and then one INSERT per feature. Strings are quoted and escaped, numbers and booleans emitted bare, nested objects and arrays written as JSON.

The intent it closes: "load this file into Postgres without installing GDAL." ogr2ogr is the right tool for a recurring pipeline; for a one-off file, a paste-able SQL script is faster and works from any machine that has psql.

How this tool works

Infer the schema, then write statements.

1. Infer the columns

Every property key in the document is collected in first-seen order and given a type from the values observed: bigint for integers, double precision for fractional numbers, boolean, jsonb for nested structures, and text for everything else. A key that holds different types across features widens to text — except integer-and-float, which widens to double precision.

2. Write the DDL

The optional CREATE TABLE adds a bigserial primary key, the property columns (or a single properties jsonb), and a geometry(Geometry, SRID) column — the generic type, since one GeoJSON file can hold mixed geometry types. It is followed by CREATE INDEX … USING GIST, without which every spatial query is a sequential scan.

3. Write the inserts

One INSERT per feature. With ST_GeomFromGeoJSON the geometry is passed as a JSON literal and wrapped in ST_SetSRID, because that function returns a geometry with SRID 0. With ST_GeomFromText the geometry is converted to WKT and the SRID passed as the second argument. A feature with a null geometry inserts NULL, and the count is reported.

Options

Geometry from

ST_GeomFromGeoJSON keeps the geometry exactly as written, including any third ordinate, and needs no conversion step — the default. ST_GeomFromText produces shorter, more portable SQL (WKT is understood by more databases than GeoJSON is) at the cost of dropping Z values.

Properties as

Typed columns gives you a normal relational table you can index and query with plain SQL — right when the schema is stable. One jsonb column keeps every property without deciding on a schema, which suits ragged data; you then query with properties->>'key' and can index it with GIN.

SRID

4326 is correct for RFC 7946 GeoJSON, which is always WGS 84. Change it only if your coordinates are actually in another system — for instance after a reprojection to 3857, where 3857 is the honest value.

Example

Input:

{"type":"FeatureCollection","features":[
  {"type":"Feature","properties":{"name":"HQ","staff":42},
   "geometry":{"type":"Point","coordinates":[-122.42,37.77]}}
]}

Output (typed columns, ST_GeomFromGeoJSON):

CREATE TABLE "features" (
  "id" bigserial PRIMARY KEY,
  "name" text,
  "staff" bigint,
  "geom" geometry(Geometry, 4326)
);
CREATE INDEX ON "features" USING GIST ("geom");

INSERT INTO "features" ("name", "staff", "geom") VALUES ('HQ', 42, ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-122.42,37.77]}'), 4326));

Note the ST_SetSRID wrapper: ST_GeomFromGeoJSON returns SRID 0, and inserting that into an SRID-constrained column fails. The generated SQL handles it for you.

Tips & common pitfalls

• Read generated SQL before running it. Values are quoted and escaped, but the DDL types are inferred from one sample — check them against what you know about the data before creating a real table.
• Big files want a real loader. Thousands of individual INSERT statements are slow. Past a few thousand features, use ogr2ogr or wrap the script in a transaction (BEGIN; … COMMIT;) to avoid a commit per row.
• Identifiers are sanitised and quoted. Property names become double-quoted identifiers, so mixed case and spaces survive — but you will need the quotes when you query them too.
• Mixed geometry types are fine. The column is declared geometry(Geometry, …). Tighten it to geometry(Polygon, 4326) by hand if your data is homogeneous and you want the constraint.

FAQ

Why ST_SetSRID around ST_GeomFromGeoJSON?

Because that function returns a geometry with SRID 0. Inserting it into a column declared with an SRID raises an error, so the SRID is set explicitly.

Which is better, GeoJSON or WKT input?

GeoJSON preserves everything including Z values and needs no conversion. WKT is more portable across databases and produces shorter statements. Both end up as the same geometry in PostGIS.

Can I use this with MySQL or SQL Server?

Not directly — the functions and types are PostGIS. The WKT option is closest to portable: swap ST_GeomFromText for your dialect's equivalent and adjust the column type.

Does it handle nested properties?

Yes — an object or array property becomes a jsonb column holding its JSON, or is included in the single properties column if you chose that mode.