search

brianc / node-pg-types

Type parsing for node-postgres
267 stars 54 forks source link

Tracking down geometry oids #147

Closed mhkeller closed 9 months ago

mhkeller commented 9 months ago

When importing shapefiles, the oid of the column type seems to differ based on the projection. Is there a list of all geometry type oids?

Steps to reproduce

  1. Download an example shapefile. You can use the New York City borough shapefile from here https://s-media.nyc.gov/agencies/dcp/assets/files/zip/data-tools/bytes/nybb_23c.zip
  2. Unzip the file
  3. Use shp2pgsql to import it with EPSG code 2263 shp2pgsql -s 2263 nybb public.nybb
  4. Query the table with select * from nybb and the oid returned by node-pg for the geom column will be 18696

If you repeat step 3 but omit the -s 2263 and requery, the oid will be 30497. If you set the epsg to 4326, the oid will be 22668.

Looking at the pg_types table, I don't see those oids even as options. Where do these come from and is there a list so that I can know whether the column type I'm querying is geometry?

brianc commented 9 months ago

I have no idea - kinda bizzare they change OIDs on columns based on the projection (I'm assuming you're using ST_SetSRID)? This really isn't specific to this library at all...I never really ended up relying on OIDs for much outside of the very standard columns because things get weird. I remember stuff like enums and other things also being kinda "dynamic" oids. Honestly didn't dive extremely deep into, just was like "yeah no that seems outta scope"

mhkeller commented 9 months ago

Yea pretty weird indeed. I wasn't using ST_SetSRID, I was just using shp2pgsql, which maybe uses that internally? I imagine ST_SetSRID would have the same effect. I'll keep playing around with it.

mhkeller commented 9 months ago

For anyone else who stumbles upon this, I think switching to the udt_name column in the information_schema.columns table may be a better way to go...