TQ
dev.com
Blog about software development
24 Oct 2016 - by 'Maurits van der Schee'
PHP-CRUD-API is a single PHP file that provides an instant powerful and consistent REST API for a MySQL, PostgreSQL or MS SQL Server databases. It now supports spatial/GIS columns and filters. PHP-CRUD-API uses reflection to “detect” the table structure and then provides an API without requiring you to write code. Spatial data is automatically detected (both on input and output) and converted from and to Well-Known-Text (WKT) format.
If you want to find the country of a specific location:
SubscribeGET http://localhost/api.php/countries?filter[]=shape,sco,POINT(30 20)
You may receive it from the database using a spatial index (if you have defined one):
[{"shape":"POLYGON((30 10,40 40,20 40,10 20,30 10))"}]
This shows how the spatial/GIS support works and also shows the WKT format for "POINT" and "POLYGON".
When I started woking in the GIS business people were using ArcView 3. Soon we moved to Oracle Spatial. When I learned about PostGIS I quickly realized that the days of specialized GIS software were numbered. Nowadays GIS is a commodity and not any different than a date field in your database. The OGC has standardized a text representation (WKT) and also defined a set of functions that most systems implement. Thanks to this standardization I was able to add a intuitive and well performing spatial/GIS functionality to PHP-CRUD-API.
SQL Server does three things different (compared to MySQL and PostgreSQL). It's spatial functions have one instead of two arguments and are implemented as methods on the geometry type. It returns "1" and "0" instead of "true" or "false". And when representing WKT it writes "POINT (30 20)" instead of "POINT(30 20)" (note the extra space before the bracket). MySQL and PostGIS seem to behave the same. SQLLite does not support spatial/GIS functionality.
I have refactored a part of PHP-CRUD-API in order to be able to add Spatial/GIS support to it. This refactoring makes it easier to add support other types. It, for instance, allows to detect the usage of "datetime" fields and ensure they are always represented in iSO 8601. It also allows to implement another much requested feature: pass 32 bit integers to JavaScript as Number instead of String.
There are still many things I want to do on this project and I have made a wiki page to keep track of them. I still need to document the newly added authentication system. I am also working on ports to Mono, .net Core or Go so that the performance even further improves. The aim is that you can use this API with the guarantee that it is and stays fast (has a flat performance profile).
PS: Liked this article? Please share it on Facebook, Twitter or LinkedIn.