CODEWITHSUNDEEP
postgresqlgisarcgisgeo
MonkeyBonkey
MonkeyBonkey

Reputation: 47861

how to import shapefile into correct Postgres geometry field type using ogr2ogr

I'm trying to import zwillow shapefiles into postgres using ogr2ogr and I see the data for the geometry coming in as a byte data type instead of a polygon or geometry.

ogr2ogr -f PostgreSQL PG:"dbname=neighborhoods" ZillowNeighborhoods-NY.shp -nln neighborhoods -append

How can I get it to imprt the geometry info into the postgres native geometry formats?

Upvotes: 2

Views: 2817

Answers (1)

Tom-db
Tom-db

Reputation: 6868

The real problem is that Postgis is not enabled/installed in your database. ogr2ogr automatically imports geometries as Postgis native format if Postgis is installed.

If Postgis is not installed, than the geometries are imported as bytea.

So the solution is: enable Postgis in your database. Read the instructions here: http://postgis.net/install/

ogr2ogr does not support this, but you can simple cast the postgis geometries in Postgres geometries. How exactly it depends which geometries are you importing. I.e you can cast Postgis polygons/multipolygons to Postgres polygons in this way:

SELECT CASE 
WHEN ST_geometrytype(wkb_geometry) = 'ST_Polygon' THEN  
wkb_geometry::polygon 
WHEN ST_geometrytype(wkb_geometry) = 'ST_MultiPolygon' THEN 
((ST_dump(wkb_geometry)).geom)::polygon  END 
FROM neighborhoods ;

Upvotes: 3

Related Questions