Stuff I'm Up To

Technical Ramblings

PostGIS Query Optimisation — March 1, 2019

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.

PostGIS and geoJson — February 22, 2019

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.

Continue reading
DBeaver – SQL GUI — November 6, 2018

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.

JIRA Software and Confluence — September 14, 2018

JIRA Software and Confluence

Installing Atlassian Jira Software onto an in-house or self-hosted server is as simple as following the Jira installation guide. The only thing missing is the setup of the database.

Jira suggest that whilst other databases are available, MySQL, MSSQL etc. their preferred DB is postgresql. Primarily because it’s common in their user space and support environment, meaning that their support and documentation is likely to be more readily available for postgresql instances than other DB’s.

Let’s follow the advice and install postgresql.

$ sudo apt-get install postgresql

At the time of writing this installs postgresql version 9.6 on Debian Stretch.

In order to create the environment that we can manage there are a couple of postgresql config changes that we make to ensure you can access the DB from another system – for managing with pgadmin 4.

Enable access to postgresql from specific network/IP addresses by editing pg_hba.conf under /etc/postgresql/9.6/main.

$ sudo vi /etc/postgresql/9.6/main/pg_hba.conf

Find the line:

host    all    all    md5

Add a line below to match your required IP addresses/subnets eg.

host    all    all  md5

This allows any machine with a 192.168.0.X address to access the DB.

Now we need to listen or bind to an IP address that is available on the network. By default postgresql only listens on port 5432, meaning it will only accept connections to the local machine from the local machine.

$ sudo vi /etc/postgresql/9.6/main/postgressql.conf

Find the line beginning:

#listen_addresses = 'localhost'

Add a new line below it:

listen_addresses = '*'

Restart the postgresql service:

$ sudo systemctl restart postgresql.service

Databases and User

Create a database and a user for Jira/Confluence to use

$ sudo -u postgres createdb jira
$ sudo -u postgres createdb confluence
$ sudo -u postgres createuser jiradb

Set the users password and grant them access to the DB’s.

$ sudo -u postgres psql 
psql (9.6.10)
Type "help" for help.

postgres=# alter user jiradb with encrypted password 'mysupersecretpassword';

postgres=# grant all privileges on database jira to jiradb;

postgres=# grant all privileges on database confluence to jiradb;

When you install Jira and confluence you can then use the database settings you’ve just created.

Database Type: PostgreSQL
Hostname:      localhost
Port:          5432
Database:      jira
Username:      jiradb
Password:      mysupersecretpassword
Schema:        public



PostGIS Import from PHP Session Fails — March 12, 2018

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!

Continue reading

PostgreSQL Copy Data Between Servers — February 20, 2018

PostgreSQL Copy Data Between Servers

Our GIS team use a PostgreSQL server with PostGIS. They recently asked if there was any way we could display some data in a simple web form for our users. So a bit of development work was required.

I didn’t want to code against their live system so thought I’d install a local version of PostGIS and copy the data from their database.

The database they wanted to access has 28 million rows – so it’s going to take a while.

Continue reading