1

According to this SO answer, the way to convert a lat/long to a geography type is

ST_SetSRID(ST_MakePoint(long, lat), 4326)::geography

but so far I've just been using ST_MakePoint(long, lat)::geography and it seems to work fine. My queries include checks for

ST_Covers(shape, ST_MakePoint(long, lat)::geography)

and I haven't seen that give an incorrect answer. But that doesn't mean it's always going to work.

The shape column is defined as

shape geography(MULTIPOLYGON, 4326)

so I thought maybe having the SRID in the column definition was doing the same work as ST_SetSRID. But I really don't know.

Probably not relevant, but just in case: the data comes from Natural Earth's countries data, converted to SQL with

shp2pgsql -GD ne_10m_admin_0_countries.shp table_name

That creates a table with a geog column also defined as geography(MULTIPOLYGON, 4326), and I've copied some of the data from that into the table with shape.

Improve this question

1 Answer 1

Reset to default
2

It works, somehow for convenience I guess. The cast implicitly assumes your geometries are in EPSG:4326; explicitly setting the original CRS and transforming is absolutely necessary when the CRS differ from WGS84. Keep in mind, though, that your geometries are not automatically referenced with a CRS, so by doing so on creation or generation you'll be on safer grounds.

As an addition:
If you define your column definition with SRID (which you should), e.g. in the process of creating an empty table, all inserts into that column will be assumed (!) to match that SRID and get assigned the identifier. As with the function though, PostGIS doesn´t care if they don´t match!
Pretty much the same applies if your table was created by script, with SRID definition, and you add geometries to the column later.
I´m curious though as to why shp2pgsql creates geography type if not explicitly specified... Didn't see the flag...

Improve this answer
3
  • So to clarify: as a special case, when a geometry hasn't had an SRID set, then casting it to geography implicitly assumes 4326; so that MakePoint()::geography approach does the same thing as SetSrid(MakePoint(), 4326)::geography? But if my lat/long weren't in 4326, or if I wanted something other than 4326, I'd need to be explicit.
    – philh
    Feb 21, 2018 at 15:34
  • 1
    "I´m curious though as to why shp2pgsql creates geography type if not explicitly specified..." - by default it creates a geometry, the -G option tells it to use geography.
    – philh
    Feb 21, 2018 at 15:35
  • 1
    @philh ah yeah. I just haven't seen it there. was it there before?...you tricked me! ,) but, you clarified correctly. (best thing I could come up with right now as to why it does essentially the same, is a discussuon in a ticket about possible auto-transformation during casting, from 8 years ago, and with a following commit to tighten the geography cast to allow only unknown or 4326 SRIDs)
    – geozelot
    Feb 21, 2018 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.