mapbox geojson -> geography instead of geometry

[IdefixRC]

Hi,

first of all awesome project and documentation. Installation and configuration worked flawlessly for me.

I'm currently trying to build a Mapbox web page that shows near real-time movements of vehicle position and struggle with getting the data from the PostGIS DB into the Mapbox GL JS. Googling for a solution I came across your program.
Now the vehicle position in the DB is stored (it comes from a 3rd party game server) in the DB as geography position data and using this data of course results in an error with your app.
Is there a way I can get around this without changing the DB ("message": "function st_transform(geography, integer) does not exist")?

In case this is not possible and you can think of another way of enabling the position data integration with mapbox I would be grateful (spend hours trying to find a solution so far.....).

Thanks a million

[tobinbradley]

That's a good question. You're the first person I've run in to using geography storage in PostGIS.

There are two options I can think of. You could either turn the geography into geometry, or you could alter the routes you're using so they don't use geometry-only functions.

For the former, the easiest thing would be to make a view of your DB table that converts the geography into geometry. That way you can have something for Dirt to grab on to without modding your table schema. Something like:

create or replace view tablegeom as
select id, etc,
  ST_GeomFromWKB(st_asbinary(geog), 4326) as geom
  
from table

Then you could query the view with Dirt as normal. You could also try passing ST_GeomFromWKB(st_asbinary(geog), 4326) as the geometry column argument in the routes you're using, and that might work as well.

That will add some overhead to your queries, as you're doing some conversion work in the database, but as long as the data isn't ginormous you probably won't notice.

The other option involves editing SQL in the routes, but if you're just using the geojson.js route it isn't terrible, and it will eliminate the data conversion overhead. Changing the sql template on geojson.js to this seems to do it:

// route query
const sql = (params, query) => {
  let bounds = query.bounds ? query.bounds.split(',').map(Number) : null;

  return `
    SELECT
      ST_AsGeoJSON(subq.*, '', ${parseInt(query.precision, 10)}) AS geojson
    FROM (
      SELECT
        ${query.geom_column} as geom
        ${query.columns ? `, ${query.columns}` : ''}
      FROM
        ${params.table},
        (SELECT ST_SRID(${query.geom_column}) AS srid FROM ${params.table} LIMIT 1) a
      ${query.filter || bounds ? 'WHERE' : ''}
        ${query.filter ? `${query.filter}` : ''}
        ${query.filter && bounds ? 'AND' : ''}
        ${bounds && bounds.length === 4 ?
          `${query.geom_column} && ST_MakeEnvelope(${bounds.join()}, 4326)`
          : ''
        }
        ${bounds && bounds.length === 3 ?
          `${query.geom_column} && ST_TileEnvelope(${bounds.join()})`
          : ''
        }
    ) as subq
  `
}

For this route all it took was getting rid of the st_transform functions, which are geometry specific. That'll be the main culprit in most routes, though there will be others (ex: st_makepoint in intersects.js is geometry specific).

Hope that helps!

[IdefixRC]

Thanks a lot for the help !! I will try this and see how it goes !

[IdefixRC]

Thanks a million. Works now and I can export the geojson to a file and read it into mapbox from there.
I was not able to use the direct URL though. It works perfectly fine for the first update but does not refresh as part of the 2second update interval, unfortunately:

var url = 'http://127.0.0.1:3000/v1/geojson/units?geom_column=position&columns=type%2C%20coalition%2Cheading&precision=9';

// Realtime update based on GeoJSON
		var request = new XMLHttpRequest();
		window.setInterval(function () 
			{
			// make a GET request to parse the GeoJSON at the url
			request.open('GET', url, true);
			request.onload = function () {
			if (this.status >= 200 && this.status < 400) 
			{
				// retrieve the JSON from the response
				var json = JSON.parse(this.response);
			 
				// update the Plane symbol's location on the map
				map.getSource('dcs').setData(json);				
			}
		};
		request.send();
		}, 2000);

[tobinbradley]

There's a cache setting in the config file that's set to 3600 seconds by default, so your browser is getting it from its cache after the first fetch. Change the cache to something lower than your desired refresh rate (like 1) and you should be good.

[IdefixRC]

Thanks this works now locally on the same PC on which the dirt server runs. Will need to figure out how to implement this without having to expose the dirt server to the net directy as the data is pulled from the client side as part of the script. For now I will pull the data and write it into a file on the webserver itself. Thanks for all the help !!
The fruits of the labor are now visible here :-) https://dcs.idefixrc.com/server_info3.php

[tobinbradley]

Cool! I'm glad it worked out.