search

systemapic / pile

PostGIS tile server
0 stars 2 forks source link

Creating tiles from raster data in PostGIS #23

Closed knutole closed 8 years ago

knutole commented 8 years ago

@strk Have continued your work on postgis_raster branch on a new branch merge/postgis_raster (which have latest commits from master) - on docker-systemapic and submodules wu, public, pile. Hope that's fine - just wanted to make sure I didn't mess up the merge. (Feel free to rebase back into postgis_raster.)

Experimenting with creating rasters:

Current xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE Map[]>
<Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0.0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">

<Style name="layer" filter-mode="first">
  <Rule>
    <RasterSymbolizer opacity="1" />
  </Rule>
</Style>
<Layer name="layer"
  srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
    <StyleName>layer</StyleName>  </Layer>

</Map>

Mapnik datasources

Starting to have doubts about it being possible to pull raster data with postgis datasource, but that this postgis-datasource is only for vector geometries? See Mapnik XMLConfigReference:

type: Specifies the format of the data source Possible values: '''shape''' : ESRI shapefile '''postgis''' : Postgis table or query '''raster''' : Tiled or stripped TIFF '''gdal''' : GDAL supported raster dataset (not build by default) '''ogr''' : OGR supported vector datasource (not build by default) '''osm''' : Open Street Map (not build by default)

It seems there's a raster datasource (for GeoTIFF files), and a gdal datasource, which - if gdal is compiled with postgis support - can pull raster from PostGIS. It may seem, however, that the postgis datasource is not meant for rasters.

Another hint pointing in this direction, is the RasterSymbolizer description:

The RasterSymbolizer is used to render an image from any GDAL supported format using the GDAL plugin or from GeoTiff's using the Raster plugin.

...which does not mention postgis datasource.

If this is true, then we probably have to:

I'm not sure about this yet, obviously. We'll have to investigate more tomorrow, @strk.

Vectorized rasters

In any case, our most pressing immediate need, is to have vectorized data from PostGIS rasters, for some simple animations etc. that we need for a client. Essentially creating regular vector tiles from PostGIS rasters.

This is more straightforward, and steps would be something like:

I will look at this tomorrow regardless.

strk commented 8 years ago

The type must be "pgraster", I just read from http://gis.stackexchange.com/questions/33746/how-to-render-postgis-raster-with-mapnik-xml, which points to a fork of mine (https://github.com/strk/mapnik/tree/2.3.x-pgraster) supposedly merged into mapnik 2.3 branch: https://github.com/mapnik/mapnik/tree/2.3.x

What version of mapnik are we shipping ?

knutole commented 8 years ago

Mapnik 3.0.9 

strk commented 8 years ago

will dig further, but could be an external plugin

strk commented 8 years ago

See "pgraster" in https://github.com/mapnik/mapnik/wiki/PluginArchitecture

strk commented 8 years ago

so, not built by default, must be enabled, better file a ticket on dockers to make it available

knutole commented 8 years ago

Found this https://github.com/mapnik/mapnik/issues/1660, looks familiar? ;)

knutole commented 8 years ago

OK, great. Let me know what you find out!

strk commented 8 years ago

yes :D

knutole commented 8 years ago

pgraster.input seems to be available in master on mapnik https://github.com/mapnik/mapnik/tree/master/plugins/input/pgraster

strk commented 8 years ago

In the XML you showe in the first commit I don't see the Datasource configuration at all (layer type and parameters). Are them supposedly added later ?

knutole commented 8 years ago

This is working xml for a PostGIS vector file. 

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE Map[]>
<Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0.0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">

<Style name="layer" filter-mode="first">
  <Rule>
    <MaxScaleDenominator>2500</MaxScaleDenominator>
    <MarkersSymbolizer allow-overlap="true" clip="false" comp-op="screen" opacity="1" fill="#ffff00" width="60" />
  </Rule>
  <Rule>
    <MaxScaleDenominator>5000</MaxScaleDenominator>
    <MinScaleDenominator>2500</MinScaleDenominator>
    <MarkersSymbolizer allow-overlap="true" clip="false" comp-op="screen" opacity="1" fill="#ffff00" width="40" />
  </Rule>
  <Rule>
    <MaxScaleDenominator>12500</MaxScaleDenominator>
    <MinScaleDenominator>5000</MinScaleDenominator>
    <MarkersSymbolizer allow-overlap="true" clip="false" comp-op="screen" opacity="1" fill="#ffff00" width="30" />
  </Rule>
  <Rule>
    <MaxScaleDenominator>25000</MaxScaleDenominator>
    <MinScaleDenominator>12500</MinScaleDenominator>
    <MarkersSymbolizer allow-overlap="true" clip="false" comp-op="screen" opacity="1" fill="#ffff00" width="20" />
  </Rule>
  <Rule>
    <MaxScaleDenominator>50000</MaxScaleDenominator>
    <MinScaleDenominator>25000</MinScaleDenominator>
    <MarkersSymbolizer allow-overlap="true" clip="false" comp-op="screen" opacity="1" fill="#ffff00" width="10" />
  </Rule>
  <Rule>
    <MaxScaleDenominator>100000</MaxScaleDenominator>
    <MinScaleDenominator>50000</MinScaleDenominator>
    <MarkersSymbolizer allow-overlap="true" clip="false" comp-op="screen" opacity="1" fill="#ffff00" width="5" />
  </Rule>
  <Rule>
    <MaxScaleDenominator>200000</MaxScaleDenominator>
    <MinScaleDenominator>100000</MinScaleDenominator>
    <MarkersSymbolizer allow-overlap="true" clip="false" comp-op="screen" opacity="1" fill="#ffff00" width="5" />
  </Rule>
  <Rule>
    <MaxScaleDenominator>400000</MaxScaleDenominator>
    <MinScaleDenominator>200000</MinScaleDenominator>
    <MarkersSymbolizer allow-overlap="true" clip="false" comp-op="screen" opacity="1" fill="#ffff00" width="2.5" />
  </Rule>
  <Rule>
    <MaxScaleDenominator>750000</MaxScaleDenominator>
    <MinScaleDenominator>400000</MinScaleDenominator>
    <MarkersSymbolizer allow-overlap="true" clip="false" comp-op="screen" opacity="1" fill="#ffff00" width="1.5" />
  </Rule>
  <Rule>
    <MinScaleDenominator>750000</MinScaleDenominator>
    <MarkersSymbolizer allow-overlap="true" clip="false" comp-op="screen" opacity="1" fill="#ffff00" width="1" />
  </Rule>
</Style>
<Layer name="layer"
  srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
    <StyleName>layer</StyleName>  </Layer>

</Map>
strk commented 8 years ago

That XML is missing a <Datasource> tag, so I don't see how can it possibly work. Did you try it with nik2img.py or mapnik-speed-check ?

strk commented 8 years ago

Example working XML file for postgis raster: http://strk.keybit.net/tmp/uint8_rgb-pgrast.xml

knutole commented 8 years ago

layer.datasource.describe() for working vector: 

{ type: 'vector',
  encoding: 'UTF8',
  fields:
   { '20130611': 'Number',
     '20130622': 'Number',
     '20130703': 'Number',
     '20130714': 'Number',
     '20130725': 'Number',
     '20130805': 'Number',
     '20130816': 'Number',
     '20130827': 'Number',
     '20130907': 'Number',
     '20130918': 'Number',
     '20130929': 'Number',
     '20131010': 'Number',
     '20131021': 'Number',
     '20131101': 'Number',
     '20140620': 'Number',
     '20140701': 'Number',
     '20140712': 'Number',
     '20140723': 'Number',
     '20140803': 'Number',
     '20140814': 'Number',
     '20140825': 'Number',
     '20140905': 'Number',
     '20141008': 'Number',
     '20141019': 'Number',
     gid: 'Number',
     code: 'String',
     lon: 'Number',
     lat: 'Number',
     height: 'Number',
     demerror: 'Number',
     r: 'Number',
     g: 'Number',
     b: 'Number',
     coherence: 'Number',
     mvel: 'Number',
     adisp: 'Number',
     dtotal: 'Number',
     d12mnd: 'Number',
     d3mnd: 'Number',
     d1mnd: 'Number' },
  geometry_type: 'point',
  srid: 3857 }
knutole commented 8 years ago

Raster:

// layer.datasource.describe():
{ type: 'raster',
  encoding: 'UTF8',
  fields: { rid: 'Number' },
  geometry_type: 'raster' }

// layer.datasource.parameters():
{ asynchronous_request: 'true',
  dbname: 'vkztdvcqkm',
  extent: '402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546',
  geometry_field: 'rast',
  host: 'postgis',
  max_async_connection: '10',
  password: 'docker',
  srid: '3857',
  table: '(SELECT * FROM file_euugtlqobovmruqdthqm) as sub',
  type: 'pgraster',
  user: 'systemapic' }
knutole commented 8 years ago

Vector:

// layer.datasource.describe():
{ type: 'vector',
  encoding: 'UTF8',
  fields:
   { '20130611': 'Number',
     '20130622': 'Number',
     // (...)
     d3mnd: 'Number',
     d1mnd: 'Number' },
  geometry_type: 'point',
  srid: 3857 
}

// layer.datasource.parameters():
{ asynchronous_request: 'true',
  dbname: 'vkztdvcqkm',
  extent: '1590352.03610997 9931604.93796449,1592807.34737174 9934460.16611465',
  geometry_field: 'the_geom_3857',
  host: 'postgis',
  max_async_connection: '10',
  password: 'docker',
  srid: '3857',
  table: '(SELECT * FROM file_iilcsjeeoyrtvzokmdmt) as sub',
  type: 'postgis',
  user: 'systemapic' 
}
knutole commented 8 years ago

Only difference I can spot is srid key on layer.datasource.describe(), present on vector but not raster.

knutole commented 8 years ago

map.toXML()

Working vector:

<?xml version="1.0" encoding="utf-8"?>
<Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over" buffer-size="128">
  <Layer name="layer" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
    <StyleName>layer</StyleName>
    <Datasource>
      <Parameter name="asynchronous_request">true</Parameter>
      <Parameter name="dbname">vkztdvcqkm</Parameter>
      <Parameter name="extent">1590352.03610997 9931604.93796449,1592807.34737174 9934460.16611465</Parameter>
      <Parameter name="geometry_field">the_geom_3857</Parameter>
      <Parameter name="host">postgis</Parameter>
      <Parameter name="max_async_connection">10</Parameter>
      <Parameter name="password">docker</Parameter>
      <Parameter name="srid">3857</Parameter>
      <Parameter name="table">(SELECT * FROM file_iilcsjeeoyrtvzokmdmt) as sub</Parameter>
      <Parameter name="type">postgis</Parameter>
      <Parameter name="user">systemapic</Parameter>
    </Datasource>
  </Layer>
</Map>

Raster:

<?xml version="1.0" encoding="utf-8"?>
<Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over" buffer-size="128">
  <Layer name="layer" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
    <StyleName>layer</StyleName>
    <Datasource>
      <Parameter name="asynchronous_request">true</Parameter>
      <Parameter name="dbname">vkztdvcqkm</Parameter>
      <Parameter name="extent">402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546</Parameter>
      <Parameter name="geometry_field">rast</Parameter>
      <Parameter name="host">postgis</Parameter>
      <Parameter name="max_async_connection">10</Parameter>
      <Parameter name="password">docker</Parameter>
      <Parameter name="srid">3857</Parameter>
      <Parameter name="table">(SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub</Parameter>
      <Parameter name="type">pgraster</Parameter>
      <Parameter name="user">systemapic</Parameter>
    </Datasource>
  </Layer>
</Map>
knutole commented 8 years ago

Getting a lot of these:

postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1340-1] systemapic@vkztdvcqkm LOG:  could not send data to client: Connection reset by peer
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1340-2] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1340-3] systemapic@vkztdvcqkm FATAL:  connection to client lost
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1340-4] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1328-1] systemapic@vkztdvcqkm LOG:  could not send data to client: Connection reset by peer
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1328-2] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1328-3] systemapic@vkztdvcqkm FATAL:  connection to client lost
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1328-4] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1352-1] systemapic@vkztdvcqkm LOG:  could not send data to client: Connection reset by peer
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1352-2] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1352-3] systemapic@vkztdvcqkm FATAL:  connection to client lost
postgis_1     | 2016-02-25 14:37:30 UTC [unknown] [1352-4] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:37:34 UTC [unknown] [1342-1] systemapic@vkztdvcqkm LOG:  could not send data to client: Connection reset by peer
postgis_1     | 2016-02-25 14:37:34 UTC [unknown] [1342-2] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:37:34 UTC [unknown] [1342-3] systemapic@vkztdvcqkm FATAL:  connection to client lost
postgis_1     | 2016-02-25 14:37:34 UTC [unknown] [1342-4] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:37:34 UTC [unknown] [1330-1] systemapic@vkztdvcqkm LOG:  could not send data to client: Connection reset by peer
postgis_1     | 2016-02-25 14:37:34 UTC [unknown] [1330-2] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:37:34 UTC [unknown] [1330-3] systemapic@vkztdvcqkm FATAL:  connection to client lost
postgis_1     | 2016-02-25 14:37:34 UTC [unknown] [1330-4] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:38:12 UTC [unknown] [1347-1] systemapic@vkztdvcqkm LOG:  could not send data to client: Connection reset by peer
postgis_1     | 2016-02-25 14:38:12 UTC [unknown] [1347-2] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:38:12 UTC [unknown] [1347-3] systemapic@vkztdvcqkm FATAL:  connection to client lost
postgis_1     | 2016-02-25 14:38:12 UTC [unknown] [1347-4] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:38:17 UTC [unknown] [1344-1] systemapic@vkztdvcqkm LOG:  could not send data to client: Connection reset by peer
postgis_1     | 2016-02-25 14:38:17 UTC [unknown] [1344-2] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:38:17 UTC [unknown] [1344-3] systemapic@vkztdvcqkm FATAL:  connection to client lost
postgis_1     | 2016-02-25 14:38:17 UTC [unknown] [1344-4] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:38:22 UTC [unknown] [1348-1] systemapic@vkztdvcqkm LOG:  could not send data to client: Connection reset by peer
postgis_1     | 2016-02-25 14:38:22 UTC [unknown] [1348-2] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:38:22 UTC [unknown] [1336-1] systemapic@vkztdvcqkm LOG:  could not send data to client: Connection reset by peer
postgis_1     | 2016-02-25 14:38:22 UTC [unknown] [1336-2] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:38:22 UTC [unknown] [1348-3] systemapic@vkztdvcqkm FATAL:  connection to client lost
postgis_1     | 2016-02-25 14:38:22 UTC [unknown] [1348-4] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:38:22 UTC [unknown] [1336-3] systemapic@vkztdvcqkm FATAL:  connection to client lost
postgis_1     | 2016-02-25 14:38:22 UTC [unknown] [1336-4] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:38:48 UTC [unknown] [1351-1] systemapic@vkztdvcqkm LOG:  could not send data to client: Connection reset by peer
postgis_1     | 2016-02-25 14:38:48 UTC [unknown] [1351-2] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
postgis_1     | 2016-02-25 14:38:48 UTC [unknown] [1351-3] systemapic@vkztdvcqkm FATAL:  connection to client lost
postgis_1     | 2016-02-25 14:38:48 UTC [unknown] [1351-4] systemapic@vkztdvcqkm STATEMENT:  SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857)
knutole commented 8 years ago

testing query in postgis: (./enter_db.sh)

vkztdvcqkm=# SELECT ST_AsBinary("rast") AS geom,"rid" FROM (SELECT * FROM file_ofcqaacgwnmxzqmfzkvs) as sub WHERE "rast" && ST_SetSRID('BOX3D(402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546)'::box3d, 3857);
Time: 45.598 ms

returns:


----------------------------------------------------------------------------------------------------
 \x01000001007761c3b6a10f90407761c3b6a10f90c0204f846dc09618413ecf421ca9ee614100000000000000000000000
(1 row)
strk commented 8 years ago 
pile_1        | ----------------
pile_1        | { asynchronous_request: 'true',
pile_1        |   dbname: 'gjnzhkasuc',
pile_1        |   extent: '402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546',
pile_1        |   geometry_field: 'rast',
pile_1        |   host: 'postgis',
pile_1        |   max_async_connection: '10',
pile_1        |   password: 'docker',
pile_1        |   srid: '3857',
pile_1        |   table: '(SELECT * FROM file_ngtmygqnuzytjcdjhdzo) as sub',
pile_1        |   type: 'pgraster',
pile_1        |   user: 'systemapic' }
pile_1        | 
pile_1        | /systemapic/dev/node_modules/redis/index.js:602
pile_1        | ----------------
pile_1        | featureset:
pile_1        | ----------------
pile_1        |                 throw err;
pile_1        |                       ^
pile_1        | 
pile_1        | /systemapic/dev/node_modules/redis/index.js:602
pile_1        |                 throw err;
pile_1        |                       ^
pile_1        | Error: PostGIS: geometry name lookup failed for table ''. Please manually provide the 'geometry_field' parameter or add an entry in the geometry_columns for ''.
pile_1        |     at /systemapic/dev/src/pile.js:1664:33
strk commented 8 years ago

The XML is lacking a style definition:

 <?xml version="1.0" encoding="utf-8"?>
 <Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over" buffer-size="128">
   <Layer name="layer" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
     <StyleName>layer</StyleName>
     <Datasource>
       <Parameter name="asynchronous_request">true</Parameter>
       <Parameter name="dbname">gjnzhkasuc</Parameter>
       <Parameter name="extent">402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546</Parameter>
       <Parameter name="geometry_field">rast</Parameter>
       <Parameter name="host">postgis</Parameter>
       <Parameter name="max_async_connection">10</Parameter>
       <Parameter name="password">docker</Parameter>
       <Parameter name="srid">3857</Parameter>
       <Parameter name="table">(SELECT * FROM file_ngtmygqnuzytjcdjhdzo) as sub</Parameter>
       <Parameter name="type">pgraster</Parameter>
       <Parameter name="user">systemapic</Parameter>
     </Datasource>
   </Layer>              
 </Map>

Consequently, the mapnik-renderer utility reports:

Error Unable to process some data while parsing 'test.xml': Style 'layer' required for layer 'layer'.
knutole commented 8 years ago

Seems we're dealing with two sets of xml here:

Created by cartoRenderer (and dumped to console here):

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE Map[]>
<Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0.0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">

<Style name="layer" filter-mode="first">
  <Rule>
    <RasterSymbolizer opacity="1" />
  </Rule>
</Style>
<Layer name="layer"
  srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
    <StyleName>layer</StyleName>  </Layer>

</Map>

And another one for the connection info (with map.toXML() here)

<?xml version="1.0" encoding="utf-8"?>
<Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over" buffer-size="128">
  <Layer name="layer" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
    <StyleName>layer</StyleName>
    <Datasource>
      <Parameter name="asynchronous_request">true</Parameter>
      <Parameter name="dbname">vkztdvcqkm</Parameter>
      <Parameter name="extent">402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546</Parameter>
      <Parameter name="geometry_field">rast</Parameter>
      <Parameter name="host">postgis</Parameter>
      <Parameter name="max_async_connection">10</Parameter>
      <Parameter name="password">docker</Parameter>
      <Parameter name="srid">3857</Parameter>
      <Parameter name="table">file_euugtlqobovmruqdthqm</Parameter>
      <Parameter name="type">pgraster</Parameter>
      <Parameter name="user">systemapic</Parameter>
    </Datasource>
  </Layer>
</Map>
strk commented 8 years ago

The style is missing because it's dumped too early, shoudl be dumped after map.fromXML is called. Dumping from _renderRasterTile gives a good one:

pile_1        | XXX preparedTile:  <?xml version="1.0" encoding="utf-8"?>
pile_1        | <Map srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0.0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over" buffer-size="128">
pile_1        |   <Style name="layer" filter-mode="first">
pile_1        |     <Rule>
pile_1        |       <RasterSymbolizer opacity="1"/>
pile_1        |     </Rule>
pile_1        |   </Style>
pile_1        |   <Layer name="layer" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
pile_1        |     <StyleName>layer</StyleName>
pile_1        |     <Datasource>
pile_1        |       <Parameter name="asynchronous_request">true</Parameter>
pile_1        |       <Parameter name="dbname">gjnzhkasuc</Parameter>
pile_1        |       <Parameter name="extent">402864.106950032 7884480.78770223,1373209.1869689 9401672.88315546</Parameter>
pile_1        |       <Parameter name="geometry_field">rast</Parameter>
pile_1        |       <Parameter name="host">postgis</Parameter>
pile_1        |       <Parameter name="max_async_connection">10</Parameter>
pile_1        |       <Parameter name="password">docker</Parameter>
pile_1        |       <Parameter name="srid">3857</Parameter>
pile_1        |       <Parameter name="table">file_ngtmygqnuzytjcdjhdzo</Parameter>
pile_1        |       <Parameter name="type">pgraster</Parameter>
pile_1        |       <Parameter name="user">systemapic</Parameter>
pile_1        |     </Datasource>
pile_1        |   </Layer>
pile_1        |   <Layer name="layer" srs="+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over">
pile_1        |     <StyleName>layer</StyleName>
pile_1        |   </Layer>
pile_1        | </Map>

Now dumping that one to an XML file on disk might make debugggin even easier :)

strk commented 8 years ago

Ok I found the XML is already saved, but when I try to use the saved xml:

root@32a9579e520c:/tmp# mapnik-render /systemapic/dev/tmp/layer_id-c77c4e60-2f4e-4cba-8fb3-e1e25f43fa39.debug.xml test2.png
Error could not zoom to combined layer extents using zoom_all because proj4 could not back project any layer extents into the map srs (set map 'maximum-extent' to override layer extents)
strk commented 8 years ago

I guess it's just saved way too early

strk commented 8 years ago

I could see some data rendering tile 0/0/0:

https://localhost/v2/tiles/layer_id-2f1bec2e-4361-4cd2-b9a7-a49c791b61eb/0/0/0.png?access_token=pk.UdcE6DCsL2QyUxLslf0bnrIt7AxxcBgbvD1i77Yg

0_0_0

strk commented 8 years ago

Ah, now I see, it's in south-africa ! Must be just a wrong projection then \o/

screenshot from 2016-02-25 17 33 09

I guess debugging now moves to the import phase ! :)

strk commented 8 years ago

It actually sounds like a swapped Y (upside-down). Common error with rasters.

strk commented 8 years ago

The location moves while zooming in, so I guess it's more a client-side issue than a server-side one. @knutole I guess it's time to switch to systemapic.js

strk commented 8 years ago

Confirmed, at zoom level 2 the tile returning the data is 2/2/1, which is the correct one ( norway is 2/2/1 on the mapbox tile ) -- so there must be some setting on the client side that position the raster tiles at the wrong place.

knutole commented 8 years ago

Yes, looks like swapped. Could be simple TMS setting. Try setting this to false https://github.com/systemapic/systemapic.js/blob/2ca3e60597ac026822ef64de65f77aecff913587/js/src/models/model.layers.js#L1014

knutole commented 8 years ago

@strk Could you push latest to postgis_raster branch?

knutole commented 8 years ago

err, or perhaps tms : true here -> https://github.com/systemapic/systemapic.js/blob/2ca3e60597ac026822ef64de65f77aecff913587/js/src/models/model.layers.js#L777

(not completely sure which Wu layer is being used for raster now)

strk commented 8 years ago

Commenting out the TMS setting fixes it. Habemus Rasters ! Please pull (--rebase) docks, git update --init --recursive and do your tests :)

knutole commented 8 years ago

@strk All tests passing.

strk commented 8 years ago

So do you want me to merge (rebased) the branch into master ?

knutole commented 8 years ago

No thanks. We still have serious memory issues - and we're using master for the other servers, so need to keep it reasonably stable. Let's see how far we get today on this branch, and then consider merging in to master.

You can merge/rebase master into postgis_raster though, if you like - there were a few merged PR's on master yesterday.

strk commented 8 years ago

I'd postpone merged unless really needed for the raster work (I struggle to keep history clean and linear).

knutole commented 8 years ago

Not really needed, no, so let's not. :+1:

strk commented 8 years ago

Tiles from raster data are now available, so closing this ticket.