_ST_BestSRID should not return negative SRID values

[strk]

After approving motion of SRID ⇐ 0 being considered UNKNOWN, we can't allow a function to return negative SRIDs with special meaning (other than UNKNOWN, that is).

See: ​http://postgis.refractions.net/pipermail/postgis-devel/2011-October/015488.html

[strk]

Found the hole being in libpgcommon:

/**
*  Given an SRID, return the proj4 text. If the integer is less than zero,
*  and one of the "well known" projections we support
*  (WGS84 UTM N/S, Polar Stereographic N/S), return the proj4text
*  for those.   
*/
static char* GetProj4String(int srid)

The function basically supports magic numbers with constant proj4text strings to save lookups in spatial_ref_sys.

I don't know what's the best way to handle this. We might always skip the lookup for those numbers, would it be acceptable ?

[strk]

The safest fix here would be to drop _ST_BestSRID and do the geography magic work at the C level. That way we wouldn't incur in srid clamping due to serialization, nor in srid clamping due to ST_SetSRID call…

[strk]

For the record: the only 2 users of _ST_BestSRID are ST_Buffer(geography, float8) and ST_Intersection(geography, geography).

[strk]

Since we have a postgis_transform_geometry function taking both source and target proj4text + target SRID, we could get around this by dropping _ST_BestSRID and replacing it with an _ST_BestProj4.

[strk]

Another option (the option above doesn't help in engaging the Proj4 cache objects) is to define a range of SRID reserved for private use (say, in the range 910000-919999) and use those, rather than negative ones. If we go with the private range, we should somehow make it clear, maybe adding records in the spatial_ref_sys table to prevent others from installin things there.

[strk]

Finding appropriate "reserved" srid space would require some inquiring on postgis-users about their current max(srid), and then enforcing the max and the min trough a check on spatial_ref_sys.

We'll have to account for:

# Builtin values (those that we ship with spatial_ref_sys.sql, which may grow in time) # Custom user values # Reserved range (which can't be added to spatial_ref_sys) # Upper limit the serialization can hold (999999)

Not sure we need to distinguish between the first two…

[strk]

The "hardcoded" values currently supported by GetProj4String (and _BestSRID) are:

#. 60 for UTM North #. 60 for UTM South #. 2 for North pole #. 2 for South pole #. 1 for World mercator

We could lay them out in a reserved 999xxx space with the following schema:

#. 999000 for World Mercator #. 999001 to 999060 for UTM North (zone: 1 to 60) #. 999061 for Lambert Azimuthal Equal Area North Pole #. 999062 for Polar Stereographic North #. 999101 to 999160 for UTM South (zone: 1 to 60) #. 999161 for Lambert Azimuthal Equal Area South Pole #. 999162 for Polar Stereographic South

[strk]

Let's see if I can get to write it nicely now:

• 999000 for World Mercator
• 999001 to 999060 for UTM North (zone: 1 to 60)
• 999061 for Lambert Azimuthal Equal Area North Pole
• 999062 for Polar Stereographic North
• 999101 to 999160 for UTM South (zone: 1 to 60)
• 999161 for Lambert Azimuthal Equal Area South Pole
• 999162 for Polar Stereographic South

[strk]

I've a patch ready to commit. The patch:

1. Writes a set of defines in libpgcommon/lwgeom_transform.h for the reserved SRID values
2. Uses the macros from _BestSRID
3. Uses the macros from ST_Transform

There were tests in tickets.sql for the correct functioning of UTM zones (thanks robe) so that part is tested. I noticed _BestSRID _never_ returns the Polar Stereographic projections.

The patch is attached here, if you want to review.

[strk]

Is it normal that the current _st_bestSRID returns -32601 for POINT(-180 0) and -32661 for POINT(180 0) ? That is basically computing from zone=1 to zone=61 ! It'd be worth preparing a proper test for the current behavior.

[strk]

I checked, and returning zone=61 is the current behavior too, so didnt' let that stop the change. With r7690 we use SRID in the 999xxx range for those hard-coded projections. The actual values are defines in libpgcommon/lwgeom_transform.h. Nothing is added to spatial_ref_sys to forbid adding such records.