Is it possible to serve uncompressed tiles? I think that's my problem (though I'm not sure).

Dirt has been awesome for serving tiles that are rendered in a browser on my computer. It works when I'm showing them in Mapbox GL and when I'm showing them in Leaflet using Leaflet.vectorGrid.

To print maps with legends and other formatting, I'm using puppeteer and chromium to pull up a hidden page with a map and print it to PDF. I can pull up the page in my regular browser just fine but not in the headless browser. For every tile, chromium produces an error "Uncaught Error: Unimplemented type: 3", source: http://127.0.0.1:8000/static/lib/leaflet-vectorgrid-1.3.0/Leaflet.VectorGrid.bundled.js (767)
This makes me think it's because the tiles are rendered compressed. mapbox/tippecanoe#793

Is there a way to send the tiles not compressed? Or do you have other ideas on why Chromium would have errors rendering Dirt vector tiles in Leaflet when it works just fine in Chrome?

Hi Tobin

Firstly, thanks for another great project! It's going to be really useful.

I've set up an API running from here: http://maps.moorthanmeetstheeye.org/api/documentation

It seems to be working fine apart from the search.

I've edited config/index.js to include this search:

mons: {
      table: 'monument_point',
      columns: `name`,
      where: `name ilike ?`,
      format: function(query) { return '%' + query.trim() + '%'; } 
    }

When I try use the search tool on the docs page I get this error.

I've tried changing the table to monument_point (the actual table as its know in the database) but that just gives me a 500 error? Where am I going wrong?

Cheers

Matt

i upload the data into postgis with srid 4326 and it works fine with the old mvt endpoint, except for a warning about not following v2 specs but when it comes to the new endpoint the data renders at the very top of the map edge as attached

Hi, thanks for the great tool !

Just a heads up: when using the geobuf route, if the query returns an empty result, there's a server error in (if using pm2) ~/.pm2/logs/index-error.log:

TypeError: Cannot read property 'length' of null
at Query.onResult ([...]/dirt/routes/geobuf.js:118:24)

My password contains the @ symbol in the db connection information. How do I handle it。
like "db": "postgres://name:WfdWc@d542dsd@ip:port/gis"

Hello, and a huge thank you for this project! It's already saved me a ton of time not having to write a back end.

I'm working on an app that needs to be hosted publicly and I'm looking at Render as a PaaS option. Render can take any GitHub repo and deploy it; so I could, for instance, point it to this repo and it will stand it up. I'm wondering what configuration would look like in that case. Is there any possibility of setting the DB connection string via environment variable, vs. writing the config/index.json file? Even if I forked this repo, I wouldn't want to commit my DB credentials to source control.

Thanks so much for any guidance on this!

We've added SSL to our server and as a consequence the api is bust. Is there something I need to to add to index.js to allow for this?

https://maps.dartmoor.gov.uk/api/documentation

Found a possible bug in this file

... FROM ${params.table}, (SELECT ST_SRID(${query.geom_column}) AS srid FROM ${ params.table ...

The SRID sub-query result might be null if there are rows in params.table where query.geom_column is null. And if that happens the outer query will always return no results, even if there are any.

Better do something like
... FROM ${params.table}, (SELECT ST_SRID(${query.geom_column}) AS srid FROM ${ params.table} WHERE ${query.geom_column} IS NOT NULL ...

i get this error when i use the service for returning geojson or the vector tiles service

{ "error": "error running query", "error_details": { "name": "error", "length": 91, "severity": "ERROR", "code": "XX000", "file": "lwgeom_transform.c", "line": "54", "routine": "transform" } }

while other services work fine, i was able to list tables in my database and return columns in a certain table etc, but what i mostly interested in is the vector tiles which gives that error, any help ?

Do you have plans to implement geocode, or reverse geocode functionality with this? What would the barrier be to say, use a list of address with coordinates (lat,long or state plane etc), and perform geocoding functions from that?

I am having some issues connecting dirt to my database. The specified user definitely has privileges to the tables but tile calls always come back as

{"statusCode":500,"error":"Internal Server Error","message":"unable to connect to database server"}

This is returned from a tile call like below to my table called nalines
http://127.0.0.1:3000/v1/mvt/nalines/6/19/24

I am running dirt locally and my connection string is like below. Could the issue be the sslmode parameter?
postgresql://myusername:mypassword@myhostlocation:25060/defaultdb?sslmode=require

My connection string should be right on and was pulled from the Digital Ocean config page where I am hosting the postgresql. Any suggestions for troubleshooting the connection further?

Hey - Nice project! I was playing around with it, and had an error with this line. PostGIS' find_srid function looks in the GEOMETRY_COLUMNS table to identify the SRID. If the geometry column was not added with AddGeometryColumns() it will not be present in the table, and will return 0 for the SRID.
Not so much a bug as a heads-up. In my case, I rewrote that line to

sql.where(`
${request.query.geom_column} && 
ST_Transform(ST_MakeEnvelope(${smBounds.join(',')} , 4326),
(SELECT ST_SRID(${request.query.geom_column}) FROM ${request.params.table} LIMIT 1))
`);

The documentation of this project is pointing to some services/files that are not available in the github repository.

Are those services (like for example "ws_geo_nearest.php") excluded for a reason, or are they waiting to be pushed to this repository?

Hi - I'm running the API through localhost: 8001. All's well with loading the documentation page. However, no matter which module I run, it returns '404'. Specifically, the url 'http://localhost/list_layers/v1' returns 'as not found on this server'. However, when I past that url into another browser page and add :8001 (http://localhost:8001/list_layers/v1) it returns the expected JSON.

I'm running a local Apache server on MacOS at localhost:80. Is there something I need to tweek in the js to correct this? Do the program's files need to be in some kind of specific folder or location? I've seen hosted apis in other Issues posts but have no idea how to configure this app. Any help would be appreciated. Thanks!

I'm using Dirt on top of a database where some tables may contain sensitive information, so I don't want to expose them via the API. @tobinbradley do you have any suggestions for what to do in that scenario? Realizing this might be out of scope for a dirt-simple server, but thought I'd ask just in case 😄

Not bulletproof per se, but I was thinking about adding a config option to my fork to disable /list_tables — not sure if that's something you'd be open to a PR on.

I noticed the Swagger docs say that /list_tables shows views as well:

however I'm not seeing views that my Dirt user has select permissions on.

I took a look a the /list_tables code and it looks like it's indeed only looking at tables. I don't have a strong use case for listing views right now so it's not an issue on my end — just wanted to flag that the docs may be out of step with the code.

There is a missing send method in bbox.js:
https://github.com/tobinbradley/dirt-simple-postgis-http-api/blob/master/routes/bbox.js#L60

reply should be reply.send

Hi. I have a PG 9.5 database with 2.3.2 PostGIS extension and a table with geo data, converted with ogr2ogr.

I'm trying to use dirt-simple-postgis-http-api as a backend for serving tiles for my map, but Mapbox GL JS can't render the vectors:

Vector tile source "source_name" layer "table_name" does not use vector tile spec v2 and therefore may have some rendering errors.

map.addSource('source', {
   'type': 'vector',
   'tiles': [
     "http://localhost:8123/mvt/v1/table_name/{z}/{x}/{y}?geom_column=wkb_geometry&limit=5000"
   ]
});

Am I doing something wrong or dirt-simple-postgis-http-api does not support MVT v2 format? Thanks

Hello again!

I am deploying Dirt to a PaaS (Render) and overall it's pretty slick to be able to point it at this repo and deploy by setting some environment variables! Lots of time saved with this new config approach 🎉

One thing I'm working through is that Dirt is having trouble connecting to my database and in order to dig in, I'm having to add some error logging to a forked copy and redeploy the app, which takes time. I've run into this before where Dirt will return a 500: Database connection error and it's a little opaque to me what's going wrong.

So, this is a somewhat hazy ask to add a server-side console.error to spit out more details when there's a problem connecting to the database. I'd be happy to take a pass at a PR on this, if it sounds helpful. Also would love to know if there's a better debugging strategy I'm not thinking of!

Got the API up and running on a new server for someone and they want to POST data to a table.

Is that something that the API can currently do?

Hello.

Could you please take a moment and give your answer on this question below.

Saving geoJSON: Specify and remember ID field #40876

i was wondering and trying to understand what's going on here and if it's the normal thing that the server tries to fetch tiles in areas outside of the data extent, so it ends up with more requests and also more error logs! which i kinda don't like it.

so i don't know if my thought is right but can attaching a bounding box to the query along with the table name would solve this ?
if what i'm saying makes sense so please guide me where i can edit to fix this.
thnx

i hit this warning in the console once a request is made for a mvt,
warning Creating a duplicate database object for the same connection
the tiles are getting delivered in one response not twice but once you set up an onclick event that returns feature properties, it returns 2 objects in the array for the same layer, so any clue on that ?
also i have a question, i noticed that you didn't put the .pbf extension on the tiles path and it works fine returning the tiles but just a thought why not adding it ?

Hi - awesome project, well done!

Have you ever come across this?

My gut feeling is that it has something to do with Multi* geom types, however, not quite sure yet...

Cheers

This is more of a question than an issue.

First off, I've had great success with this application for number of projects. Nice work!

2nd, I don't really understand how/where to setup caching. Any suggestion for a beginner route to getting my tiles cached etc?

Hi,

Any plans for adding Geobuf (lossless) feature !,

Geobuf provides nearly lossless compression of GeoJSON data into protocol buffers. Advantages over using GeoJSON alone:
as mentioned in :
https://github.com/mapbox/geobuf

as I know there is a function in PostGIS : ST_AsGeobuf
https://postgis.net/docs/ST_AsGeobuf.html

I hope if it can be added in future release as a route

Thanks

I've got Dirt-Simple (DS) set up for one table and I'm able to run several of the functions on the geometry.

I can't get mvt to work because I don't know what to fill in as values for z, x, and y.

I've filled in some values from a PNG image tile from another map that I'm using on a production site, so I believe those values represent a real place in my dataset.

Instead of a good response, I get this error:

{
  "error": "error running query",
  "error_details": {
    "name": "error",
    "length": 370,
    "severity": "ERROR",
    "code": "42501",
    "where": "SQL statement \"select SRID         from geometry_columns where (f_table_schema = schem or schem = '') and f_table_name = tabl and f_geometry_column = $3\"\nPL/pgSQL function find_srid(character varying,character varying,character varying) line 15 at SQL statement",
    "file": "aclchk.c",
    "line": "3409",
    "routine": "aclcheck_error"
  }
}

I've just upgraded to the newer version and hit a snag.

Getting a empty screen and this error in my console:

api:40 Uncaught ReferenceError: SwaggerUIBundle is not defined at window.onload (api:40)

See for yourself: http://all-mapped-out.co.uk/api

I'm running a proxy on my apache server using these parameters (from an older version):

ProxyPass /api http://localhost:3000
ProxyPass /swaggerui http://localhost:3000/swaggerui

And these options in my config:

"swagger": {
    "basePath": "/api",
    "externalDocs": {
      "url": "https://github.com/tobinbradley/dirt-simple-postgis-http-api",
      "description": "Source code on Github"
    }

Might be really something simple I'm missing but I'm totally stumped!

Hi Tobin

I want to add the mvt_v1.js to my server without doing a complete rebuild. Is this possible?

Thanks
Matt

If Dirt can't connect to the database, it should error in the console where it's running. There should be a verbose mode for seeing every request and potentially even more info.

The issue I thought I had (#41) was because Dirt was returning some sort of error response, which Leaflet couldn't interpret as a tile. Googling Leaflet's error led me to think Dirt was compressing the tile and somehow headless browsers couldn't handle that.

Logging would have avoided the rabbit hole I went down for days.

I've added the search below to index.js

 planapps: {
      table: 'planapp_points',
      columns: `ogc_fid as id,
                      appcode as label,
                      apptype as type,
                      round(ST_X(ST_Transform(geom, 4326))::NUMERIC,4) as lng,
                      round(ST_Y(ST_Transform(geom, 4326))::NUMERIC,4) as lat,
                      round(ST_X(ST_Transform(geom, 27700))::NUMERIC,4) as easting,
                      round(ST_Y(ST_Transform(geom, 27700))::NUMERIC,4) as northing`,
      where: `appcode ilike ?`,
      format: function(query) { return '%' + query.trim() + '%'; }
}

The search uses appcodes which are formed like so: 0482/15. Every entry will have the / character.
When test this on the docs page get the error below. Do I need to format the query somehow?

Hello, I'm trying to get geojson data, all requests works fine besides request when no data. in that requests their responses statuses always is pending.

Quick look for code showed me the response code must be 204 when result.rows.length === 0, but 204 not back

First off like to thank you for this project.

Not sure if I am inputting things wrong but when I tried to get nearest point I get this

{
"statusCode": 500,
"code": "XX000",
"error": "Internal Server Error",
"message": "0 is an invalid target SRID"
}

This is my request URL http://127.0.0.1:3100/v1/nearest/ala_23/-171.7776728%2C-13.83859403%2C4326?geom_column=geom&columns=Name&limit=10

My SRID is 4326. Not sure why the target SRID is needed?

Where do I get version 3 please?

Thanks

I am migrating from dirt v2 (hapi) to v3 (fastify). The GeoJSON route works fine on v2, but fails on v3. I noticed that the SQL is more involved in v3. Perhaps the issue lies there?

Here's my error. The Nearest route works fine, so dirt can definitely see the database.

$ curl -X GET "http://localhost:8123/v1/geojson/public.mytable?geom_column=geom&columns=*"

{"statusCode":500,"error":"Internal Server Error","message":"SELECT * with no tables specified is not valid"}/

Hi,

I tried two tables with two feature types polygon and points, and used the mvt route with mapbox gl js,

when trying to render the mvt layer, it shows but encounters some discontinuity while rendering on the map,

• no (min,max) zoom level constraints in mapbox sample

Expected

I can overwrite the default geometry column geom

Actual

I cannot use a different name for the geometry column than geom (wkb_geometry, the default ingestion by OGR, does not work).

Steps to reproduce

1. Try to access the MVT route on a database with a geometry column named other than geom -> ERROR 500 "column \"geom\" does not exist"
2. Rename table to geom

parcels=> ALTER TABLE wallonia_2018 RENAME COLUMN wkb_geometry TO geom;
ALTER TABLE

3. Try to access the MVT route again -> Works

Screenshots

Screenshot of Swagger with input wkb_geometry

Screenshot of Swagger with input geom after renaming column

Using ES2015 template strings might make the SQL query readable, but it is NOT SAFE!

The end user of the API has control over req.query and req.params, and can therefore execute any SQL query/procedure they want.

Make sure to use a parameterized query when using postgres-node (pg).

This will make sure that whatever the user passes in will always be interpreted as data, and never as a command.

Example from your code:

Hi tobin, i noticed your updates today to the server and i'm about to use it in a project but i'm concerned about how to implement authentication on a certain or all the end points.
since the api is written in hapi framework so i'm looking for resources about that if you have good ones.
also if you have a plan to implement this in the future ?

i wanted to give the mvt endpoint a shot after the last query optimization video, the performance is great i barley notice any CPU usage ! but there's a problem in rendering data at zoom levels before 7
tried with multiple data sources imported using db manager in qgis, targeted SRID 4326.
data is rendered offset-ed and distorted at zoom level 0,1,2 with each zoom level you hit the offset goes back to right place until zoom level 7
can you please see if you can replicate this ?

*** system running postgresql 10.6 and postgis 2.5

Hi, just read you post on GEOJSON there

It seems that node-mapnik allows you to directly query a postgis datasource and transform it directly into vector tiles (see the docs). Why do you then go through the hassle of first converting your query results to GeoJson, and then to a vector tile ?

I am asking the question because I never managed to do it properly from a postgis datasource with node-mapnik, just from a shape datasource (and it works well). I was wondering what were your views on this.

This is not an issue but kind of need help with ideas, i have a scenario that requires serving multiple layers as a stacked pbf tile, currently the mvt endpoint accepts only one table for the query, i wonder if the query can be tweaked to serve OSM data along side other layers.
i do understand that there are options out there for serving OSM but in my case i have to bake other layers that ( changed too much ) in the same tile, so i can't do mbtiles from openmaptiles

I would love to see support for the feature id parameter in the mvt endpoint
Since it's required for the function setfeaturestate in the mapbox gl js api
And i found that postgis has support for that but i couldn't figure out how to add it and make a PR.

Hello Tobey!

I guess the title says it all. Here is a screenshot of the request:

I've tried encoding the URI but it has not worked. I did noticed that there is a variable named "q" on the the geobuf.js class (line 50) and mvt.js line 12 and 46, but even changing the variable name to something else does not seem to work. Any help you could provide would help me a lot since I've been stuck on this for days...

Anyway to sanitize the column input?

Thank you!

Currently running the "postgis2x" branch.
node version: v12.14.1
npm version: 6.13.4

Error message:
{
"statusCode": 500,
"code": "XX000",
"error": "Internal Server Error",
"message": "pgis_asmvt_transfn: parameter row cannot be other than a rowtype"
}

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

I have an AWS Postgres instance db.m6g.large (2vCPU and 8GiB memory) that I am working with. I am doing some tests with a pretty big dataset. This dataset has global coverage and 400million polylines. They have a unique ID which is indexed and I can easily query on that and make my selections in just a second or so some selections result in . The bottleneck comes when waiting for Postgres to build and send those tiles back.

I add a filter on the indexed unique ID to my tile calls. e.g.
https://tiles.myserver.xyz/v1/mvt/mylinestable/5/28/16?&filter=uid = '25790'

This sometimes takes at least a minute or two. Some of these selections could contain 15,000 lines so I am guessing this is one of the issues.

Any quick suggestions for improving performance? The tables have already been spatially indexed. I am just thinking I need to increase class of my database on AWS but am hoping there are some cheaper things to try first.

Thanks!

Hi!
I am trying to serve some tiles from PostGIS. So I cloned the repo and installed dependencies, then set up DB config, and fired it up.
I can access the Swagger UI docs and can query the DB on the meta section: I can list tables and columns.
It is not working properly, however, when I query using /v1/mvt/{table}/{z}/{x}/{y} to return MVTs.
I am getting a 200 response of "Unrecognized response type; displaying content as text." while testing it out in the features section of the documentation.

/v1/geojson/{table} returns features when specifying bounds using a Z/X/Y tile (0,0,0).

But /v1/geobuf/{table} also returns "Unrecognized response type; displaying content as text." and it looks like:

"���
k
i���e���X������������������������������������������������������������������
��
�����}�ߙY�Ԓ�������������������������������������������������������������������������������
��
�������ǝ�X�ƒ������������������������������������������ ��������������������������������������������������������������������������������������������
��

I am getting the same response when trying to consume the MVT by setting up a tile layer in a mapboxgl.Map instance.

Any pointers?
Thanks a lot!

Will this read all tables in all schemas within a DB if they have a geometry column?

Hi. I see next warning while using dirt-simple-postgis-http-api:

WARNING: Creating a duplicate database object for the same connection.
at handler (/home/bulai/projects/dirt-simple-postgis-http-api/routes/mvt_v1.js:73:18)
at Object.internals.handler (/home/bulai/projects/dirt-simple-postgis-http-api/node_modules/hapi/lib/handler.js:96:36)
at request._protect.run (/home/bulai/projects/dirt-simple-postgis-http-api/node_modules/hapi/lib/handler.js:30:23)
at internals.Protect.run (/home/bulai/projects/dirt-simple-postgis-http-api/node_modules/hapi/lib/protect.js:64:5)
at exports.execute (/home/bulaj/projects/dirt-simple-postgis-http-api/node_modules/hapi/lib/handler.js:24:22)
at each (/home/bulai/projects/dirt-simple-postgis-http-api/node_modules/hapi/lib/request.js:383:16)

I took a look at the routes/mvt_v1.js and saw that it initialize a DB connection everytime handler called. I think it can be placed somewhere at the top to prevent memory leaks and in order to optimize the speed of the server.