I’m yak shaving this morning, and one of the yaks I need to ensmooth is running a PHP script that connects to a PgSQL database.
No problem, OSX ships with PHP! Oh wait, that PHP does not include PgSQL database support.
At this point, you can either run to completely replace your in-build PHP with another PHP (probably good if you’re doing modern PHP development and want something newer than 5.5) or you can add PgSQL to your existing PHP installation. I chose the latter.
The key is to build the extension you want without building the whole thing. This is a nice trick available in PHP, similar to the Apache module system for independent module development.
First, figure out what version of PHP you will be extending:
Then, unbundle it and go to the php extension directory:
tar xvfz php-5.5.38.tar.bz2
cd php-5.5.38/ext/pgsql
Now the magic part. In order to build the extension, without building the whole of PHP, we need to tell the extension how the PHP that Apple ships was built and configured. How do we do that? We run phpize in the extension directory.
> /usr/bin/phpize
Configuring for:
PHP Api Version: 20121113
Zend Module Api No: 20121212
Zend Extension Api No: 220121212
The phpize process reads the configuration of the installed PHP and sets up a local build environment just for the extension. All of a sudden we have a ./configure script, and we’re ready to build (assuming you have installed the MacOSX command-line developers tools with XCode).
> ./configure \
--with-php-config=/usr/bin/php-config \
--with-pgsql=/opt/pgsql/10
> make
Note that I have my own build of PostgreSQL in /opt/pgsql. You’ll need to supply the path to your own install of PgSQL so that the PHP extension can find the PgSQL libraries and headers to build against.
When the build is complete, you’ll have a new modules/ directory in the extension directory. Now figure out where your system wants extensions copied, and copy the module there.
Finally, you need to edit the /etc/php.ini file to enable the new module. If the file doesn’t already exist, you’ll have to copy in the template version and then edit that.
sudo cp /etc/php.ini.default /etc/php.ini
sudo vi /etc/php.ini
Find the line for the PgSQL module and uncomment and edit it appropriately.
Now you can check and see if it has picked up the PgSQL module.
> php --info | grep PostgreSQL
PostgreSQL Support => enabled
PostgreSQL(libpq) Version => 10.0
PostgreSQL(libpq) => PostgreSQL 10.0 on x86_64-apple-darwin15.6.0, compiled by Apple LLVM version 8.0.0 (clang-800.0.42.1)
Whoops, it snuck by me in the laze of summer, but the BC Public Accounts have come out, so I can do a (partial) update of my IT outsourcing summary. Why “partial”? Because I cannot include Health Region spending until their vendor spending summaries are released late in the year. So this summary is for central government only.
The year-over-year trend is flat, which means that last year’s steep drop-off of spending on IBM dominates the look of the chart.
The chart by vendor gives a better feel for who is up and who is down:
IBM, up a little year-over-year, but still way down after last years’ collapse. ESIT continues to dominate all vendors by a large margin. Note that ESIT is the new name for HP Advanced Solutions (HPAS) which was itself the new name for the BC operations of EDS.
Maximus is up a little, but with the MSP premium program (and thus the associated administration contract) potentially winding down it’s hard to imagine any long term trend for them but down. At a minimum the 50% cut in premium rates effectively doubles the administrative overhead represented by the Maximum contract, which is not a good look.
One thing I’m going to be looking at once the health numbers are in is whether billings by Cerner move up to compensate for the drop-off by IBM. By rights they should: Cerner has taken over the huge EHR project at PHSA/Coastal. On the other hand, I heard a rumour that much of that spending was shifted out to a “non-profit” entity by the BC Liberals, which would make it disappear from my survey of vendor payments reports.
TL;DR: If you are using ORDER BY or GROUP BY on geometry columns in your application and you have expectations regarding the order or groups you obtain, beware that PostGIS 2.4 changes the behaviour or ORDER BY and GROUP BY. Review your applications accordingly.
The first operators we learn about in elementary school are =, > and <, but they are the operators that are the hardest to define in the spatial realm.
When is = equal?
For example, take “simple” equality. Are geometry A and B equal? Should be easy, right?
But are we talking about:
A and B have exactly the same vertices in the same order and with the same starting points?
A and B have exactly the same vertices in any order? (see ST_OrderingEquals)
A and B have the same vertices in any order but different starting points?
A has some extra vertices that B does not, but they cover exactly the same area in space? (see ST_Equals)
A and B have the same bounds?
Confusingly, for the first 16 years of its existence, PostGIS used definition 5, “A and B have the same bounds” when evaluating the = operator for two geometries.
However, for PostGIS 2.4, PostGIS will use definition 1: “A and B have exactly the same vertices in the same order and with the same starting points”.
Why does this matter? Because the behavour of the SQL GROUP BY operation is bound to the “=” operator: when you group by a column, an output row is generated for all groups where every item is “=” to every other item. With the new definition in 2.4, the semantics of GROUP BY should be more “intuitive” when used against geometries.
What is > and <?
Greater and less than are also tricky in the spatial domain:
Is POINT(0 0) less than POINT(1 1)? Sort of looks like it, but…
Is POINT(0 0) less than POINT(-1 1) or POINT(1 -1)? Hm, that makes the first one look less obvious…
Greater and less than are concepts that make sense for 1-dimensional values, but not for higher dimensions. The “>” and “<” operators have accordingly been an ugly hack for a long time: they compared the minima of the bounding boxes of the two geometries.
If they were sortable using the X coordinate of the minima, that was the sorting returned.
If they were equal in X, then the Y coordinate of the minima was used.
Etc.
This process returned a sorted order, but not a very satisfying one: a “good” sorting would tend to place objects that are near to each other in space, near to each other in the sorted set.
Here’s what the old sorting looked like, applied to world populated places:
The new sorting system for PostGIS 2.4 calculates a very simple “morton key” using the center of the bounds of a feature, keeping things simple for performance reasons. The result is a sorted order that tends to keep spatially nearby features closer together in the sorted set.
Just as the “=” operator is tied to the SQL GROUP BY operation, the “>” and “<” operators are tied to the SQL ORDER BY operation. The pictures above were created by generating a line string from the populated places points as follows:
Protobuf based output formats for PostGIS, because these are new in PostGIS 2.4 and I haven’t read the commits, might as well get some info straight from the horse’s mouth.
FOSS4G from the Trenches, because the belly of the GIS beast is the local government market, hearing how a practitioner has cracked that most uncrackable and conservative of audiences is very interesting.
