PostGIS SQL Dump
Open BetaPostGIS SQL is a text SQL script that creates a table with a geometry column and fills it with data, ready to load into any PostgreSQL database with the PostGIS extension. It is a convenient way to hand an OSM selection to a team already on the PostgreSQL stack: apply the script and you immediately have a geometry table for queries through ST_* functions. Our exporter generates a self-contained .sql: CREATE TABLE with a geom column and attributes, then one INSERT per feature where geometry is given via ST_GeomFromText(WKT, SRID) (SRID 4326 by default). Multiple layers become separate tables, and values are escaped (SQL-injection safe). Honestly: this is a simple human-readable dump via ST_GeomFromText, not a binary pg_dump archive and not the full osm2pgsql schema with planet_osm tables and spatial indexes.
Vigtigste funktioner
Om formatet PostGIS SQL
PostGIS SQL is not a binary dump but an ordinary text script: create a table with a geometry column and fill it with rows. It can be read by eye, put into version control and applied with a single psql command on any PostgreSQL database with the PostGIS extension.
The format is convenient when data is handed to a team already living on PostgreSQL: analytics, backend, portal developers. Apply the script — and you immediately have a table that ST functions work on: intersections, buffers, distances, aggregates over the area.
The script is self-contained: it enables the PostGIS extension if it is not there yet, creates a table for each layer with a geom column and attributes, inserts objects through ST_GeomFromText with the coordinate system specified (EPSG:4326 by default) and at the end creates a GiST spatial index on geom. Values are escaped and field types are mapped onto PostgreSQL types.
Two details it is better to know in advance. Before creating a table the script runs DROP TABLE IF EXISTS with a cascade — if the schema already contains a table with the same name, it will be deleted along with the dependent objects, so apply the script into a separate schema or check the names. And the volume: up to 500,000 objects are carried over per table, beyond that the export is truncated — for large territories take GeoPackage or FlatGeobuf and load them through ogr2ogr.
Hvilket program åbner PostGIS SQL-filer
Følgende programmer og applikationer kan åbne, redigere og arbejde med PostGIS SQL Dump-filer (.sql), der er eksporteret fra OSM2CDR:
Format specifications
- Open standard
- 2D only
- Lossless (no quality loss)
- Stores feature attributes
- Output: 1 file
- A self-contained .sql script (CREATE TABLE + INSERT via ST_GeomFromText, SRID 4326) with escaped values; a human-readable dump, not a binary pg_dump or osm2pgsql schema.
Hvem bruger PostGIS SQL-kort
Sådan eksporterer du PostGIS SQL
Sådan åbner du filen efter eksporten
- psql — create the database, run CREATE EXTENSION postgis, then psql -d your_database -f dump.sql; the layer tables appear right away together with the indexes.
- DBeaver or pgAdmin — open the file as a script and run it as a whole, if working in a terminal is inconvenient.
- Before applying it — look through the beginning of the file: it shows which tables will be dropped and created anew. It is safer to deploy the export into a separate schema.
- QGIS — connect to the database (Layer -> Add PostGIS Layer) and work with the data straight from there, with no intermediate files.
- GeoServer — publish the table as a WMS or WFS layer for an internal portal.
Populære kort i PostGIS SQL
Få korteksport i PostGIS SQL Dump
Download professionelle PostGIS SQL-kort fra OpenStreetMap. Øjeblikkelig generering, ethvert område.