sqlite source in mapbox classic (follow up to #1492)

[bmehta]

This is a follow-up to #1492 that was closed yesterday. Attention @springmeyer.

I imported my spatialite (essentially sqlite) database into Mapbox Studio Classic to eventually convert to mbtiles. This database contains multiple tables, each with the same table strucutre. Each table can have multiple rows.

I entered the appropriate subquery, which is a union of all these tables i.e.

'(
select * from area_hydrography
union
select * from area_lm
union
select * from linear_feat
union
select * from....
.
.
.)

However, it looks like mapbox studio is surrounding this with...

select * from
( [subquery above])
limit 1.

I accidentally found this because of a typo I made in the table name and saw this error...

Sqlite Plugin: 'no such table: zcta' (/Users/mehtab/projects/dbs/spatialLite_sprint7/1_24001000100.sqlite)
Full sql was: 'SELECT * FROM (  (select * from area_hydrography
  union
  select * from area_lm
  union
  select * from linear_feat
  union
  select * from linear_hydrography
  union
  select * from place
  union
  select * from point_lm
  union
  select * from roads_gen
  union
  select * from roads_sec
  union
  select * from tabblock
  union
  select * from tract
  union
  select * from zcta)
) LIMIT 1'

I am not sure why it is doing a limit 1? This would not account for multiple rows in the same table, let alone, a union of all the tables. Getting rid of the surrounding round brackets throws no error, but seems to not show the layer at all.

Is there a way for me to account for multiple rows?

Sqlite file is attached. Please remove .png from the extension (thats the only way I could get it to upload)

[springmeyer]

The LIMIT 1 is coming from Mapnik - which handles the database access: https://github.com/mapnik/mapnik/blob/master/plugins/input/sqlite/sqlite_datasource.cpp#L211-L221. What mapnik needs to know are the attribute names. And therefore it needs to issue this initial query. Unfortuntely with SQLITE API we have to get at least one row returned to get info on attributes. I think what you are trying to do is reasonable but a poorly testing path. My recommendation would be to move to PostGIS (the Mapnik PostGIS driver is better tested) or do your union ahead of time, create a view, and then just reference that view as your table in Mapbox Studio Classic.

[bmehta]

Thanks for the quick response. So the "LIMIT 1" is just so it can get info on attributes? But eventually it does take into account all rows to finally export to mbtiles? (All the columns across all tables are exactly the same, I am just wondering if all the rows will be taken into account for the eventual export).

[springmeyer]

So the "LIMIT 1" is just so it can get info on attributes?

Yes

But eventually it does take into account all rows to finally export to mbtiles?

Yes, that is the intention

(All the columns across all tables are exactly the same, I am just wondering if all the rows will be taken into account for the eventual export).

Yes.

[bmehta]

I am seeing some issues with sqlite (essentially spatialite) as the source in Mapbox Studio (v0.3.2). It seems like once I import the spatialLite file, the boundary seems clipped. I have 4 attachments:

Map1: How the final output is supposed to look (This is derived by importing the .sqlite file below in QGIS)

Map2: The screenshot from Mapbox Studio

State.sqlite.png: The original spatialite file (Please remove .png extension)

State.mbtiles.png: the export from Mapbox Studio (Please remove .png extension)

Is there something I am doing wrong? Or something I should be doing differently? Thanks.

Binita

[wilhelmberg]

@bmehta

I think there is something wrong with your sqlite db.

1. I cannot display it in QGIS and I get these metadata information:

2. If I export it in QGIS via DB Manager to shapefile it displays in QGIS, but as soon as I add any other data, it disappears.

3. Problem seems to be the coordinate system of the data. Looking into the database shows a srid of 40004, that is not supported by QGIS or Mapbox Studio Classic.

Mapbox Studio Classic needs your sqlite data to be in either WGS84/EPSG:4326 or Web Mercator/EPSG:3857/EPSG:900913

[bmehta]

@BergWerkGIS

Thanks very much for the detailed explanation.

[bmehta]

@BergWerkGIS Can I get some more help with this? I was able to get the source spatialite file with the WGS84 coordinate system. I was able to import it into mapbox and see the state boundaries (MD, VA, West VA etc.). However, when I export it out to mbtiles, I fail to see the state boundaries in my application. Are you able to take a look and let me know if I am doing something wrong?

state.sqlite.png : The original spatialite file (please remove .png extension)

state.mbtiles.png : The export from mapbox studio (please remove .png extension)

statepreview.png : The screenshot from mapbox studio

Thanks.

[wilhelmberg]

let me know if I am doing something wrong?

Everything ok, your mbtiles is looking good and contains data.

However, when I export it out to mbtiles, I fail to see the state boundaries in my application.

MBTiles exported from a source project contain vector tiles and not bitmap tiles. Does your application handle vector tiles?

More information about vector tiles: https://www.mapbox.com/developers/vector-tiles/ https://github.com/mapbox/vector-tile-spec

Applications that can handle vector tiles: https://github.com/mapbox/awesome-vector-tiles

If you want to have bitmaps in your mbtiles you have to create a Style Project from your source in Mapbox Studio Classic. (Mapbox Studio Classic Guide: https://www.mapbox.com/guides/getting-started-studio/)

After that you could use tl to export your style to a mbtiles database that contains bitmap tiles.

[bmehta]

@BergWerkGIS Yes, the application handles mbtiles. It was an issue with the way I named my layers in the styles json - its fixed now. Thanks again for your help.

[wilhelmberg]

Great that you got it working. :+1: