How to express the projection using pure sql?

Question

I have used

RGEO_FACTORY = RGeo::Geographic.simple_mercator_factory
RGEO_FACTORY.point(lon, lat).projection

But now I want to use sql to implement it. And I have the point(geometry) in the postgis database.

How should I do ?

Answer 1

RGeo simple mercator factory docs state that it uses SRID 4326 to store points and SRID 3785 for projections. PostGIS has ST_Transform for that. Here's a nice tutorial covering the topic. Suppose you store your geom field in 'nodes' table and it's in SRID 4326, then you can use ST_Transform to get the same projection as RGeo simple mercator. Use ST_SRID to learn which is your geom field's srid:

select geom, ST_SRID(geom) from nodes limit 1;
                        geom                        | st_srid 
----------------------------------------------------+---------
 0101000020E61000004A97FE25A9523E40B6B9D683EEE74D40 |    4326

select ST_Transform(geom, 3785) from nodes limit 1;
                    st_transform                    
----------------------------------------------------
 0101000020C90E0000FE8D2A88D4C04941A418472F1AE25F41

If you try to convert geom to the same srid, it doesn't change:

select ST_Transform(geom, 4326) from nodes limit 1;
                    st_transform                    
----------------------------------------------------
 0101000020E61000004A97FE25A9523E40B6B9D683EEE74D40