DataTables is a fantastic software that turns any HTML table into an interactive and editable one.
Chicago Cityscape uses DataTables on pretty much every page. DataTables also provides server-side processing for PHP to grab the right data from a database and show it to the user, 10 records at a time (the number of records can be changed by the user to show more records at a time).
Problem
One of the problems I’ve had to get around is that the DataTables Editor script recognizes a SELECT
statement with only one function per field. If there’s a second function that’s manipulating a field in the SELECT
statement then the ALIAS
won’t work and the JavaScript will not be able to read and show the data properly.
I almost always use the two functions ST_AsGeoJSON
and ST_Transform
on my geographic data to (1) transform it from the SRID of 3435 to 4326 for displaying on web mercator maps, and (2) converting the GIS data into GeoJSON strings for easy transference to the Leaflet JavaScript library.
This is a known issue – see this thread on the DataTables forums – that Allan, the DataTables creator, has acknowledged and provided an alternative solution for.
Solution
It turns out that it’s easy to write a function that combines both functions.
CREATE OR REPLACE FUNCTION ST_TAGJP (field geometry, srid int =4326, simplify int =5)
RETURNS text
RETURN ST_AsGeoJSON(ST_Transform(field, srid), simplify);
The code above is a function I call ST_TAGJP
that combines the two functions I already described, and is flexible by letting the user specify in the arguments the table and field, the SRID to transform (reproject) to, and the simplify variable that can be used in ST_AsGeoJSON
that tells it how many decimal points to use in coordinates.