How to export feature GeoJSON from PostGIS table?

How to export feature GeoJSON from PostGIS table?

Closed 4 years ago. I’d like to do a GeoJSON dump of a PostGIS table, but I want to export features (the geometry and the properties) not just the geometry. I’ve been digging into the ST_AsGeoJSON function but it looks like I can only get the coordinate information (which makes sense since it’s a geometry function).

How to store GeoJSON featurecollection in PostgreSQL 9.3?

Assuming you have at least PostgreSQL version 9.3, you can use a few JSON functions and operators to extract the relevant parts of the GeoJSON specification required by ST_GeomFromGeoJSON to create geometries. Finds three geometries. The geom column has the geometry object, and the gid is the feature number.

How to assign srid to GeoJSON featurecollection?

You should assign an SRID for the geometry, using ST_SetSRID. Or if you simply need a single heterogeneous GEOMETRYCOLLECTION, you can make it compact like this:

How to build a JSON object in PostgreSQL?

This can be done a bit more simply with json_build_object in PostgreSQL 9.4+, which lets you build up a JSON by supplying alternating key/value arguments. For example: Things get even better in PostgreSQL 9.5+, where some new operators are added for the jsonb data type ( docs ).

Can a PostGIS command be used in OGR?

PostGIS is one of the supported read/write OGR formats, so it is possible to use the OGR API or any OGR commands (such as ogrinfo and ogr2ogr) to manipulate its datasets.

What are the parameters of the ogr2ogr command?

The ogr2ogr command has many options and parameters; in this recipe, you have seen some of the most notable ones such as -f to define the output format, -t_srs to reproject/transform the dataset, and -sql to define an (eventually spatial) query in the input OGR dataset.

How to import ISO2 features in ogr2ogr?

Using some of the options from ogr2ogr, you will import only the features from SUBREGION=2 (Africa), and the ISO2 and NAME attributes, and rename the feature class to africa_countries:

How to bring in geometry part of GeoJSON?

You can use PostGIS’s ST_GeomFromGeoJSON to bring in just the geometry part of the GeoJSON. ogr2ogr -f “PostgreSQL” PG:”dbname=my_database user=postgres” “source_data.json” -nln destination_table -append

How does St _ geomfromgeojson work for JSON fragments?

ST_GeomFromGeoJSON works only for JSON Geometry fragments. It throws an error if you try to use it on a whole JSON document. Enhanced: 3.0.0 parsed geometry defaults to SRID=4326 if not specified otherwise.

Is there way to import geousa data directly or through MongoDB?

I already imported geousa data available in this public dump into MongoDB smoothly. Let me know is there a way to import this data directly or through MongoDB? You can use PostGIS’s ST_GeomFromGeoJSON to bring in just the geometry part of the GeoJSON.

Can a row be returned as a GeoJSON feature?

Return the geometry as a GeoJSON “geometry” object, or the row as a GeoJSON “feature” object. (Cf GeoJSON specifications RFC 7946 ). 2D and 3D Geometries are both supported. GeoJSON only support SFS 1.1 geometry types (no curve support for example).

Where can I find the GeoJSON format format?

GeoJSON format is popular among web mapping frameworks. You can test and view your GeoJSON data online on geojson.io. To build FeatureCollection: To get Features as records:

How to run PostGIS to shapefile dumper?

Pgsql2shp (postgis to shapefile dumper) is command that provides shapefile and takes query as input. The geometry column should be present when running the postgis query. You need to download zip file to run pgsql2shp.

How to export shapefile from PostgreSQL command line?

Similarly you can import the shapefile in postgresql using the command line tool shp2pgsql. Export shapefile from postgreSQL – pgsql2shp. Here we will check pgsql2shp tool that can be used either with command line or with php language. Similarly you may connect to postgresql with server side language and use pgsql2shp.

Which is the output of St _ Union-PostGIS?

The output may be a single geometry, a MultiGeometry, or a Geometry Collection. Comes in several variants: Two-input variant: returns a geometry that is the union of two input geometries. If either input is NULL, then NULL is returned. Array variant: returns a geometry that is the union of an array of geometries.

How to return the geometry as a GeoJSON?

ST_AsGeoJSON — Return the geometry as a GeoJSON element. text ST_AsGeoJSON( record feature, text geomcolumnname, integer maxdecimaldigits=9, boolean pretty_bool=false); Return the geometry as a GeoJSON “geometry” object, or the row as a GeoJSON “feature” object.

Is the maxdecimaldigits argument used in GeoJSON?

(Cf GeoJSON specifications RFC 7946 ). 2D and 3D Geometries are both supported. GeoJSON only support SFS 1.1 geometry types (no curve support for example). The maxdecimaldigits argument may be used to reduce the maximum number of decimal places used in output (defaults to 9).