Migrate PostGIS / C code to PL/pgSQL

Open-Historical-Map-Labs / openhistoricalmap-docker

A dockerized configuration to create the OpenHistoricalMap backend

9 stars 3 forks source link

Migrate PostGIS / C code to PL/pgSQL #7

Open jimmyrocks opened 5 years ago

jimmyrocks commented 5 years ago

Existing C code will not be supported by Amazon RDS, it can be rewritten in PL/pgSQL and run as SQL functions. Test this code to make sure it produces the same results as the C code and that the performance hit is minimal.

danrademacher commented 5 years ago

@jimmyrocks what's the latest on this? Do you need any help from @mojodna?

danrademacher commented 5 years ago

Notes here:

Jim has 2 of 3 functions working, but one for converting Transaction IDs to int4 is problematic
Used for the replication pipeline.

danrademacher commented 5 years ago

MapTileforPoint -- working
TileforPoint -- mostly working, but close
XidtoInt4 -- not clear what this is for, but more work here later.

danrademacher commented 5 years ago

looks like first 2 in next 2 days

jimmyrocks commented 5 years ago

These updates were made with this commit: https://github.com/OpenHistoricalMap/openhistoricalmap-docker/commit/ded9806d872d8c8ce6aeb626577229f22265ba03

timwaters commented 5 years ago

https://github.com/openstreetmap/osmosis/blob/master/package/script/contrib/apidb_0.6_osmosis_xid_indexing.sql seems to be a replacement for the C version? although I've not run them both side by side to see they appear to be the same...

This is the C version:

select xid_to_int4('123'::xid);
 xid_to_int4 
-------------
         123

I think the C xid function is used exclusively by osmosis when it can connect directly to an API db

timwaters commented 5 years ago

recently in the main osm website repo they have added the sql functions https://github.com/openstreetmap/openstreetmap-website/blob/master/db/functions/functions.sql