CODEWITHSUNDEEP
gispostgisgdalogr2ogr
Brettk80
Brettk80

Reputation: 1

OGR2OGR PostgreSQL / PostGIS issue after enabling postgis extension on import

I'm running psql (PostgreSQL) 14.5 (Homebrew) with PostGIS extension version 3.3

I'm using gdal's ogr2ogr to import geojson files.

ogr2ogr -f "PostgreSQL" PG:"dbname=test4 user=myuser" "myfile.geojson"

If I import all files into a new database and enable the postgis extension after all my imports, my queries work as desired.

SELECT district, ST_Contains('POINT (-##.## ##.## )', wkb_geometry) FROM table

Returns: booleans as expected

If I import another geojson file after the extension is enabled, I get an error on the query for new tables imported.

ERROR: contains: Operation on mixed SRID geometries (Point, 0) != (Polygon, 4326) SQL state: XX000

It seems it changes the column type from bytrea to geometry and doesn't allow me to alter or disable the extension. I have to delete the database and import all tables again, then enable the extension. What am I doing wrong? Is there a problem in my process or query? Why does it work if I import the data and then enable the extension, but all new tables fail with the query?

Upvotes: 0

Views: 511

Answers (1)

Ian Turton
Ian Turton

Reputation: 10976

You should be using something like:

SELECT district, 
   ST_Contains(ST_GeomFromText('POINT (-##.## ##.## )',4326), wkb_geometry) 
FROM table

This make PostGIS aware that your WKT is in lon,lat coordinates and so it can safely compare them to your geometry column (which will be the same because that's what GeoJSON contains by specification). Other data sources may be in different projections in which case you'll probably need to read about ST_TRANSFORM too.

Upvotes: 0

Related Questions