PostGIS Query Optimisation

I started to do some work on how efficient the spacial SQL queries I was using are. That meant looking at the indexing that is used on the geometry column and understanding the functions I used a bit more.

I made a simple change from using ST_Contains to ST_Within and made a tenfold increase in the performance of my query. Similarly with a change from ST_Distance to ST_DWithin.

Now if only I read the documentation more!

Prior to 1.3, ST_Expand was commonly used in conjunction with && and ST_Distance to achieve the same effect and in pre-1.3.4 this function was basically short-hand for that construct. From 1.3.4, ST_DWithin uses a more short-circuit distance function which should make it more efficient than prior versions for larger buffer regions.

https://postgis.net/docs/ST_DWithin.html

PostGIS and geoJson

On with this current theme of spacial systems and mapping, one of the interesting challenges I faced was querying spacial data.

My particular problem was trying not to publish masses of polygons up onto an Esri map layer and make the data layer too cumbersome for our intended visitors purpose.

We have a lot of plots of land that we care for and needed to check that when a web site visitor clicks on the Esri map that we check to see if the location they have clicked actually belongs to a plot we manage.

DBeaver – SQL GUI

I’ve used a few SQL GUI’s over the years, SQuirreL, DBVisualizer, HeidiSQL, MySQL Workbench, but the one that stands out recently is DBeaver.

It’s got a community and enterprise edition. The community does everything I need and connects to all the SQL servers we use, Microsoft SQL, MySQL, Postgres/PostGIS.

Being Java based it’s cross platform, so you can use it in Windows too.

PostGIS Import from PHP Session Fails

When importing a CSV file using Laravel I found that I’d get some strange error messages relating to a called function not matching the parameters that I was sending.

local.ERROR: SQLSTATE[42883]: Undefined function: 7 ERROR: function st_makepoint(numeric, numeric) does not exist
LINE 1: SELECT ST_SetSRID(ST_MakePoint(NEW.x_coordinate, NEW.y_coord...
 ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I narrowed it down to the ST_SetSRID() and ST_MakePoint() functions and was 100% sure that they existed in the database, and with the right parameter types. I tried casting the values to double precision and still and the same errors.

The solution turned out to be that the function existed in the public schema NOT the schema I was using!