We’ve all got SQL that needs tidying up.
It wont fix your syntax but it will make it look nice.
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_Within and made a tenfold increase in the performance of my query. Similarly with a change from
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
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
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.
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
$ sudo vi /etc/postgresql/9.6/main/pg_hba.conf
Find the line:
host all all 127.0.0.1/32 md5
Add a line below to match your required IP addresses/subnets eg.
host all all 192.168.0.0/24 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 127.0.0.1 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
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
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: 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_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!
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.