Subscribe to this thread
Home - General / All posts - on-the-fly reprojection or a better option for coordinate conversion in SQL
artlembo


3,400 post(s)
#16-Nov-17 17:26

I want to send something in to tech for an enhancement, but wanted to get other people's opinion. It covers two things:

1. Why not just allow on-the-fly projection in SQL like 8 did. Man, would that make life simpler. I would be interested in the reasoning for not doing it.

2. If on-the-fly will not be done in SQL, then the SQL engine is similar to PostGIS, which quite frankly, I don't mind. But, it is so much easier in PostGIS. For example, this is what I had to write in MF:

I am taking all the taxi pickup locations and computing the distance to the dropoff locations, and then comparing them to the reported distance. I am also grouping those average deviations by taxi zone.

Now, the pickup and dropoff geometries are in lat/lon - so I can't report the distance in feet or meters (again, DistanceEarth in 8 was great for this - even PostGIS has a toggle that returns the ellipsoid distance, so that would be nice too). And, the taxi zone areas are not in lat/lon, so the GeomTouches will not work. Therefore, I have to do a projection inside the SQL:

FUNCTION Conv() TABLE AS

  CALL CoordConverterMake(ComponentCoordSystem([taxi_pickup]),'EPSG:2263')

END;

SELECT zone,

    AVG(GeomDistance(CoordConvert(CALL Conv(), [pickup_geometry]), CoordConvert(CALL Conv(), [dropoff_geometry]), 0)/5280 

     - [trip_distance]) as diffDistance

  FROM [taxi_january_2014],[taxi_zones]

  WHERE GeomTouches(CoordConvert(CALL Conv(), [pickup_geometry]), [Geom], 0)

GROUP BY zone

THREADS 8

Functions are cool for sure. But what a PIA to write this and issue all the CALLS and even keep track of the commas (I'm assuming a completed MF will have some intellisense to help the user know about closing parenthesis, and commas). It truly is annoying. Now, with the ST_Transform in PostGIS, things would look like this:

SELECT zone,

    AVG(ST_Distance(ST_Transform(pickup_geometry,2263),

                    ST_Transform(dropoff_geometry,2263)) 

        - [trip_distance])) as diffDistance

  FROM taxi_january_2014,taxi_zones

  WHERE ST_Intersects(ST_Transform(pickup_geometry,2263),Geom)

GROUP BY zone

The PostGIS way is much simpler to write, and if you used ST_Distance(geom,geom,1) you would not have to issue the ST_Transform. And, if MF just did this on the fly, the entire thing would look like this:

SELECT zone,

    AVG(GeomDistance(pickup_geometry],[dropoff_geometry]), 0)/5280 

     - [trip_distance]as diffDistance

  FROM [taxi_january_2014],[taxi_zones]

  WHERE GeomTouches([pickup_geometry]), [Geom], 0)

GROUP BY zone

THREADS 8

The other thing I don't like is that the FUNCTION has to be written for every projection. What if the taxi_zones weren't in EPSG 2263, but rather in UTM 18. Now you have to create another CoordinateConverter to run that transform. In PostGIS, ST_Transform simply takes the coordinate system you start in, and reprojects it to whatever SRID you wish to send it to.

Also, since GeomDistance returns feet, I have to do the little divide by 5280 trick. Not a problem, but 8 also allowed us to return KM, MI, FT, CHAINS, etc.

I'm sure we could think up a couple of reasons to keep it the way it is, but I'm willing to bet that 99% of the time, a user is going to want to run this thing like the simpler method I proposed.

tjhb
10,094 post(s)
#16-Nov-17 20:26

These are really good questions Art. On the whole I agree. It needs streamlining.

On the other hand I will have to play devil's advocate a bit and point out some errors. The current SQL interface isn't actually as bad as you say. (More to come.)

adamw


10,447 post(s)
#01-Dec-17 10:56

This threads seemingly continued in Discussion Question 3.

Here is my reply in that thread: the reply.

Two additional notes specific to this thread:

1. You don't have to create a new function to create a converter for each pair of coordinate systems. Queries generated by the Transform pane use a single function with no parameters simply because it is more convenient for the pane to generate the code that way (not a strong preference here either, it can generate the code in a different way easily). You can create a function which will accept either of the two coordinate systems as a parameter or just use no intermediate functions and call CoordConverterMake directly.

2. The calls to ST_Transform in PostGIS look as simple as they do for two reasons: because they are limited to encoding the coordinate system as an integer value which you have to set up first, and because geometry values store these integer values which you have to write into them as well. The first reason is key here (the second wouldn't exist without it) and it is limiting in many other ways, it is just that the limitations are outside of the query and we aren't seeing them in the example.

If you are willing to, say, restrict the coordinate systems you can use to EPSG codes, it is trivial to write the equivalent of ST_Transform in Radian / Future, you just have to pass two systems instead of one: ST_Transform(geom, sourceEpsg, targetEpsg)...

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.