search

osmlab / yalcha

A Go implementation of openstreetmap-cgimap
4 stars 5 forks source link

Use existing cgimap SQL patterns #3

Closed pnorman closed 6 years ago

pnorman commented 6 years ago

We want to replicate the existing functionality, which means yalcha shouldn't need any SQL migrations, nor rely on SQL functions. If there are SQL functions that are needed, those should be installed via the existing SQL migration mechanisms which live in the rails port.

hesidoryn commented 6 years ago

I make this migrations using goose only because it's easier for me right now. If SQL functions will be needed they will be installed via rails port of course. Or we can replace these functions with prepared statements.

Can we experiment with new SQL queries and patterns? I compared response time for yalcha and cgimap on some requests, for example relation/#id/full and way/#id/full. Yalcha is more than 2x and 10x respectively faster than cgimap, and it is without some special efforts. All single elements requests are also more than 2-3x faster than cgimap.

way/4904965/full:

relation/1155955/full:

pnorman commented 6 years ago

If SQL functions will be needed they will be installed via rails port of course. Or we can replace these functions with prepared statements.

Use prepared statements, this fits the programming patterns used in cgimap and the rails port.

Can we experiment with new SQL queries and patterns?

SQL improvements are okay, but it's not the point of this project. Keep the same general pattern of breaking each call into component parts and then reusing those component parts. e.g. the map call does nodes from bbox, ways from nodes, nodes from way nodes, relations from ways, relations from nodes, relations from relations; the relations full call selects relations from id, nodes from relations, ways from relations, nodes from way nodes, relations members. These two calls have parts in common. Of course, the SQL will differ between the readwrite backend and the readonly backend because the latter can't use temp tables, but the parts are called in the same order.

Where more changes might be more appropriate is the output SQL, because we've got different bindings than libpqxx.

We don't want to do the type of SQL in that fastmap proposal.

hesidoryn commented 6 years ago

made it https://github.com/osmlab/yalcha/pull/15