styling based on attribute for MapServer derived MVTiles

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

styling based on attribute for MapServer derived MVTiles

Karsten-3-2
Dear All,
 
I was working on the configuration of MapServer generated MVT tiles for a demo country world map and had success with MS v 7.2 outputting tiles in MVT format ( all thanks to https://github.com/sdlime/mvt-demo ).
I am trying to work on the client side style for my demo map (using mapbox-gl.js) and defined them in a similar way as JSON as in Steve's mvt-demo above. The whole thing is later intended to be used in a benchmark for MapServer MVT rendering speed versus T-Rex ( see http://blog.sourcepole.ch/2018/09/06/the-tale-of-a-vector-tile-benchmark/ ) .
 
For now I am struggling with specifying the correct style syntax for the client side and it seem that I missing something as I was not yet able to successfully use attribute properties to render Countries as different colors based on an attribute field of my PostGIS source.
Below is the configuration of my 'allcountries' layer. The mapcolor7 filed is an integer field (in the input PostGIS source) but my set-up fails to output multiple colors , instead I am getting all default black for the country fill color. A second layer where I specified only Italy as one Country 'country-italy' to show up renders fine -  see https://github.com/karstenv/mvt-benchmark/blob/master/MapServer/countries_mvt.png
 
 
Would anyone know what I need to change in order to render the countries with different colors according to the 'mapcolor7' attribute ?
To me it seems that somehow the attribute is not available to the renderer even though in the MapServer map file I am selecting all fields in the sub-query ...
 
Thanks
Karsten
 
--------------------------------------------
{
    "id": "country-fill",
    "type": "fill",
    "source": "compass",
    "source-layer": "allcountries",
    "paint": {
      "fill-color": {
        "property": "mapcolor7",
        "stops": [
            [1, "#fdaf6b"],
            [2, "#fdc663"],
            [3, "#fae364"],
            [4, "#d3e46f"],
            [5, "#aadb78"],
            [6, "#a3cec5"],
            [7, "#ceb5cf"]
        ]
      }
    }
},{
    "id": "country-italy",
    "type": "fill",
    "source": "compass",
    "source-layer": "italy",
    "paint": {
      "fill-color": "#fdc663"
      }
  }
 
Karsten Vennemann
Terra GIS

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: styling based on attribute for MapServer derived MVTiles

Lime, Steve D (MNIT)

I believe the default WxS behavior is to expose no attributes. You need to be explicit and I don’t see the gml_include_items metadata property in the mapfile for the layer allcountries. You’d need something like:

 

“gml_include_items”    “mapcolor7”

 

Added to the allcountries layer. The mvt-demo does this for most layers…

 

--Steve

 

From: mapserver-users [mailto:[hidden email]] On Behalf Of karsten
Sent: Monday, February 04, 2019 4:33 PM
To: [hidden email]
Subject: [mapserver-users] styling based on attribute for MapServer derived MVTiles

 

Dear All,

 

I was working on the configuration of MapServer generated MVT tiles for a demo country world map and had success with MS v 7.2 outputting tiles in MVT format ( all thanks to https://github.com/sdlime/mvt-demo ).

I am trying to work on the client side style for my demo map (using mapbox-gl.js) and defined them in a similar way as JSON as in Steve's mvt-demo above. The whole thing is later intended to be used in a benchmark for MapServer MVT rendering speed versus T-Rex ( see http://blog.sourcepole.ch/2018/09/06/the-tale-of-a-vector-tile-benchmark/ ) .

 

For now I am struggling with specifying the correct style syntax for the client side and it seem that I missing something as I was not yet able to successfully use attribute properties to render Countries as different colors based on an attribute field of my PostGIS source.

Below is the configuration of my 'allcountries' layer. The mapcolor7 filed is an integer field (in the input PostGIS source) but my set-up fails to output multiple colors , instead I am getting all default black for the country fill color. A second layer where I specified only Italy as one Country 'country-italy' to show up renders fine -  see https://github.com/karstenv/mvt-benchmark/blob/master/MapServer/countries_mvt.png

 

 

Would anyone know what I need to change in order to render the countries with different colors according to the 'mapcolor7' attribute ?

To me it seems that somehow the attribute is not available to the renderer even though in the MapServer map file I am selecting all fields in the sub-query ...

 

Thanks

Karsten

 

--------------------------------------------

{
    "id": "country-fill",
    "type": "fill",
    "source": "compass",
    "source-layer": "allcountries",
    "paint": {
      "fill-color": {
        "property": "mapcolor7",
        "stops": [
            [1, "#fdaf6b"],
            [2, "#fdc663"],
            [3, "#fae364"],
            [4, "#d3e46f"],
            [5, "#aadb78"],
            [6, "#a3cec5"],
            [7, "#ceb5cf"]
        ]
      }
    }
},{
    "id": "country-italy",
    "type": "fill",
    "source": "compass",
    "source-layer": "italy",
    "paint": {
      "fill-color": "#fdc663"
      }
  }

 

Karsten Vennemann
Terra GIS


_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: styling based on attribute for MapServer derived MVTiles

Karsten-3-2
Great, Thanks Steve
I added  "gml_include_items" "mapcolor7"
to the Metadata block and got it working.
One quirk seems to be that when I am trying to the values as integer (from the mapcolor7 column) specifying the style as in 1.) below did not work.
However it I treat them as characters instead, quoted them and used "type": "categorical" in the json file for the style as in 2.) below it is working.
 
Cheers
Karsten

1.)
----------------------------
{
    "id": "country-fill",
    "type": "fill",
    "source": "compass",
    "source-layer": "allcountries",
    "paint": {
      "fill-color": {
        "property": "mapcolor7",
        "stops": [
            [1, "#fdaf6b"],
            [2, "#fdc663"],
            [3, "#fae364"],
            [4, "#d3e46f"],
            [5, "#aadb78"],
            [6, "#a3cec5"],
            [7, "#ceb5cf"]
        ]
      }
    }
}
 
2.)
----------------------------
{
    "id": "country-fill",
    "type": "fill",
    "source": "compass",
    "source-layer": "allcountries",
    "paint": {
      "fill-color": {
        "property": "mapcolor7",
        "type": "categorical",
        "stops": [
            ["1", "#fdaf6b"],
            ["2", "#fdc663"],
            ["3", "#fae364"],
            ["4", "#d3e46f"],
            ["5", "#aadb78"],
            ["6", "#a3cec5"],
            ["7", "#ceb5cf"]
        ]
      }
    }
}


From: Lime, Steve D (MNIT) [mailto:[hidden email]]
Sent: Tuesday, February 05, 2019 21:15
To: karsten; [hidden email]
Subject: RE: [mapserver-users] styling based on attribute for MapServer derived MVTiles

I believe the default WxS behavior is to expose no attributes. You need to be explicit and I don’t see the gml_include_items metadata property in the mapfile for the layer allcountries. You’d need something like:

 

“gml_include_items”    “mapcolor7”

 

Added to the allcountries layer. The mvt-demo does this for most layers…

 

--Steve

 

From: mapserver-users [mailto:[hidden email]] On Behalf Of karsten
Sent: Monday, February 04, 2019 4:33 PM
To: [hidden email]
Subject: [mapserver-users] styling based on attribute for MapServer derived MVTiles

 

Dear All,

 

I was working on the configuration of MapServer generated MVT tiles for a demo country world map and had success with MS v 7.2 outputting tiles in MVT format ( all thanks to https://github.com/sdlime/mvt-demo ).

I am trying to work on the client side style for my demo map (using mapbox-gl.js) and defined them in a similar way as JSON as in Steve's mvt-demo above. The whole thing is later intended to be used in a benchmark for MapServer MVT rendering speed versus T-Rex ( see http://blog.sourcepole.ch/2018/09/06/the-tale-of-a-vector-tile-benchmark/ ) .

 

For now I am struggling with specifying the correct style syntax for the client side and it seem that I missing something as I was not yet able to successfully use attribute properties to render Countries as different colors based on an attribute field of my PostGIS source.

Below is the configuration of my 'allcountries' layer. The mapcolor7 filed is an integer field (in the input PostGIS source) but my set-up fails to output multiple colors , instead I am getting all default black for the country fill color. A second layer where I specified only Italy as one Country 'country-italy' to show up renders fine -  see https://github.com/karstenv/mvt-benchmark/blob/master/MapServer/countries_mvt.png

 

 

Would anyone know what I need to change in order to render the countries with different colors according to the 'mapcolor7' attribute ?

To me it seems that somehow the attribute is not available to the renderer even though in the MapServer map file I am selecting all fields in the sub-query ...

 

Thanks

Karsten

 

--------------------------------------------

{
    "id": "country-fill",
    "type": "fill",
    "source": "compass",
    "source-layer": "allcountries",
    "paint": {
      "fill-color": {
        "property": "mapcolor7",
        "stops": [
            [1, "#fdaf6b"],
            [2, "#fdc663"],
            [3, "#fae364"],
            [4, "#d3e46f"],
            [5, "#aadb78"],
            [6, "#a3cec5"],
            [7, "#ceb5cf"]
        ]
      }
    }
},{
    "id": "country-italy",
    "type": "fill",
    "source": "compass",
    "source-layer": "italy",
    "paint": {
      "fill-color": "#fdc663"
      }
  }

 

Karsten Vennemann
Terra GIS


_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: styling based on attribute for MapServer derived MVTiles

Lime, Steve D (MNIT)
You can specify a type in the gml metadata and that will impact how the conversion is done in the mvt encoding. Something like:

   gml_mapcolor7_type integer

Otherwise things are treated as strings...

—Steve

From: karsten <[hidden email]>
Sent: Tuesday, February 5, 2019 3:01:51 PM
To: [hidden email]
Cc: Lime, Steve D (MNIT)
Subject: RE: [mapserver-users] styling based on attribute for MapServer derived MVTiles
 
Great, Thanks Steve
I added  "gml_include_items" "mapcolor7"
to the Metadata block and got it working.
One quirk seems to be that when I am trying to the values as integer (from the mapcolor7 column) specifying the style as in 1.) below did not work.
However it I treat them as characters instead, quoted them and used "type": "categorical" in the json file for the style as in 2.) below it is working.
 
Cheers
Karsten

1.)
----------------------------
{
    "id": "country-fill",
    "type": "fill",
    "source": "compass",
    "source-layer": "allcountries",
    "paint": {
      "fill-color": {
        "property": "mapcolor7",
        "stops": [
            [1, "#fdaf6b"],
            [2, "#fdc663"],
            [3, "#fae364"],
            [4, "#d3e46f"],
            [5, "#aadb78"],
            [6, "#a3cec5"],
            [7, "#ceb5cf"]
        ]
      }
    }
}
 
2.)
----------------------------
{
    "id": "country-fill",
    "type": "fill",
    "source": "compass",
    "source-layer": "allcountries",
    "paint": {
      "fill-color": {
        "property": "mapcolor7",
        "type": "categorical",
        "stops": [
            ["1", "#fdaf6b"],
            ["2", "#fdc663"],
            ["3", "#fae364"],
            ["4", "#d3e46f"],
            ["5", "#aadb78"],
            ["6", "#a3cec5"],
            ["7", "#ceb5cf"]
        ]
      }
    }
}


From: Lime, Steve D (MNIT) [mailto:[hidden email]]
Sent: Tuesday, February 05, 2019 21:15
To: karsten; [hidden email]
Subject: RE: [mapserver-users] styling based on attribute for MapServer derived MVTiles

I believe the default WxS behavior is to expose no attributes. You need to be explicit and I don’t see the gml_include_items metadata property in the mapfile for the layer allcountries. You’d need something like:

 

“gml_include_items”    “mapcolor7”

 

Added to the allcountries layer. The mvt-demo does this for most layers…

 

--Steve

 

From: mapserver-users [mailto:[hidden email]] On Behalf Of karsten
Sent: Monday, February 04, 2019 4:33 PM
To: [hidden email]
Subject: [mapserver-users] styling based on attribute for MapServer derived MVTiles

 

Dear All,

 

I was working on the configuration of MapServer generated MVT tiles for a demo country world map and had success with MS v 7.2 outputting tiles in MVT format ( all thanks to https://github.com/sdlime/mvt-demo ).

I am trying to work on the client side style for my demo map (using mapbox-gl.js) and defined them in a similar way as JSON as in Steve's mvt-demo above. The whole thing is later intended to be used in a benchmark for MapServer MVT rendering speed versus T-Rex ( see http://blog.sourcepole.ch/2018/09/06/the-tale-of-a-vector-tile-benchmark/ ) .

 

For now I am struggling with specifying the correct style syntax for the client side and it seem that I missing something as I was not yet able to successfully use attribute properties to render Countries as different colors based on an attribute field of my PostGIS source.

Below is the configuration of my 'allcountries' layer. The mapcolor7 filed is an integer field (in the input PostGIS source) but my set-up fails to output multiple colors , instead I am getting all default black for the country fill color. A second layer where I specified only Italy as one Country 'country-italy' to show up renders fine -  see https://github.com/karstenv/mvt-benchmark/blob/master/MapServer/countries_mvt.png

 

 

Would anyone know what I need to change in order to render the countries with different colors according to the 'mapcolor7' attribute ?

To me it seems that somehow the attribute is not available to the renderer even though in the MapServer map file I am selecting all fields in the sub-query ...

 

Thanks

Karsten

 

--------------------------------------------

{
    "id": "country-fill",
    "type": "fill",
    "source": "compass",
    "source-layer": "allcountries",
    "paint": {
      "fill-color": {
        "property": "mapcolor7",
        "stops": [
            [1, "#fdaf6b"],
            [2, "#fdc663"],
            [3, "#fae364"],
            [4, "#d3e46f"],
            [5, "#aadb78"],
            [6, "#a3cec5"],
            [7, "#ceb5cf"]
        ]
      }
    }
},{
    "id": "country-italy",
    "type": "fill",
    "source": "compass",
    "source-layer": "italy",
    "paint": {
      "fill-color": "#fdc663"
      }
  }

 

Karsten Vennemann
Terra GIS


_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Help with failing Postgis query...

Brent Wood-7

Hi,


I'm getting an error with an SQL in a mapfile.


The query works from from the command line:


select ROW_NUMBER() OVER (order by code) as id, code, sci_name, com_name, lower(family_com)||' ('|| family_sci||')' as Family, point from rdb.species_master s, maps.null_point p where code in (select distinct species from trawl.t_catch) limit 10 ;


id,code,sci_name,com_name,family,locn
1,AAT,Alainopasiphaea australis,Alainopasiphaea australis,,0101000020E610000000000000000000000000000000000000

...


When embedded in a mapfile & invoked from a browser or QGIS I get an error:


WFS server error. ms_error->code not found
msPostGISLayerWhichShapes(): Query error. Error executing query.



Any suggestions?


Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI:  +64 (4) 3860529




Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
T +64-4-386-0529  
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade, Greta Point, Wellington
Connect with NIWA: niwa.co.nz Facebook Twitter LinkedIn Instagram
To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.





_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with failing Postgis query...

Stephen Woodbridge-4
Brent,

Are you wrapping your query like:

DATA "select * from (select ROW_NUMBER() OVER (order by code) as id,
code, sci_name, com_name, lower(family_com)||' ('|| family_sci||')' as
Family, point from rdb.species_master s, maps.null_point p where code in
(select distinct species from trawl.t_catch) limit 10) as foo using
unique id using srid=4326"

where srid is appropriate for your data?

-Steve W

On 2/10/2019 7:51 PM, Brent Wood wrote:

>
> Hi,
>
>
> I'm getting an error with an SQL in a mapfile.
>
>
> The query works from from the command line:
>
>
> select ROW_NUMBER() OVER (order by code) as id, code, sci_name,
> com_name, lower(family_com)||' ('|| family_sci||')' as Family, point
> from rdb.species_master s, maps.null_point p where code in (select
> distinct species from trawl.t_catch) limit 10 ;
>
>
> id,code,sci_name,com_name,family,locn
> 1,AAT,Alainopasiphaea australis,Alainopasiphaea
> australis,,0101000020E610000000000000000000000000000000000000
>
> ...
>
>
> When embedded in a mapfile & invoked from a browser or QGIS I get an
> error:
>
>
> WFS server error. ms_error->code not found
> msPostGISLayerWhichShapes(): Query error. Error executing query.
>
>
>
> Any suggestions?
>
>
> Brent Wood
>
> Programme leader: Environmental Information Delivery
> NIWA
> DDI:  +64 (4) 3860529
>
>
> <http://www.niwa.co.nz>
>
>
> Brent Wood
> Principal Technician - GIS and Spatial Data Management
> Programme Leader - Environmental Information Delivery
> T +64-4-386-0529
>
> National Institute of Water & Atmospheric Research Ltd (NIWA)
> 301 Evans Bay Parade, Greta Point, Wellington
> Connect with NIWA: niwa.co.nz <https://www.niwa.co.nz>Facebook
> <https://www.facebook.com/nzniwa>Twitter
> <https://twitter.com/niwa_nz>LinkedIn
> <https://www.linkedin.com/company/niwa>Instagram
> <https://www.instagram.com/niwa_science>
>
> To ensure compliance with legal requirements and to maintain cyber
> security standards, NIWA's IT systems are subject to ongoing
> monitoring, activity logging and auditing. This monitoring and
> auditing service may be provided by third parties. Such third parties
> can access information transmitted to, processed by and stored on
> NIWA's IT systems.
>
>
>
>
>
>
> _______________________________________________
> mapserver-users mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/mapserver-users


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with failing Postgis query...

Lime, Steve D (MNIT)
Also, I'd turn on debugging. The PostGIS code is very good about writing meaningful debugging information to that log file.

-----Original Message-----
From: mapserver-users [mailto:[hidden email]] On Behalf Of Stephen Woodbridge
Sent: Sunday, February 10, 2019 11:04 PM
To: [hidden email]
Subject: Re: [mapserver-users] Help with failing Postgis query...

Brent,

Are you wrapping your query like:

DATA "select * from (select ROW_NUMBER() OVER (order by code) as id,
code, sci_name, com_name, lower(family_com)||' ('|| family_sci||')' as
Family, point from rdb.species_master s, maps.null_point p where code in
(select distinct species from trawl.t_catch) limit 10) as foo using
unique id using srid=4326"

where srid is appropriate for your data?

-Steve W

On 2/10/2019 7:51 PM, Brent Wood wrote:

>
> Hi,
>
>
> I'm getting an error with an SQL in a mapfile.
>
>
> The query works from from the command line:
>
>
> select ROW_NUMBER() OVER (order by code) as id, code, sci_name,
> com_name, lower(family_com)||' ('|| family_sci||')' as Family, point
> from rdb.species_master s, maps.null_point p where code in (select
> distinct species from trawl.t_catch) limit 10 ;
>
>
> id,code,sci_name,com_name,family,locn
> 1,AAT,Alainopasiphaea australis,Alainopasiphaea
> australis,,0101000020E610000000000000000000000000000000000000
>
> ...
>
>
> When embedded in a mapfile & invoked from a browser or QGIS I get an
> error:
>
>
> WFS server error. ms_error->code not found
> msPostGISLayerWhichShapes(): Query error. Error executing query.
>
>
>
> Any suggestions?
>
>
> Brent Wood
>
> Programme leader: Environmental Information Delivery
> NIWA
> DDI:  +64 (4) 3860529
>
>
> <https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.niwa.co.nz&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&amp;sdata=muvSp2QB5z9I7aenwMwa%2F5DmbW5M7wXVD%2BabvBeIS8A%3D&amp;reserved=0>
>
>
> Brent Wood
> Principal Technician - GIS and Spatial Data Management
> Programme Leader - Environmental Information Delivery
> T +64-4-386-0529
>
> National Institute of Water & Atmospheric Research Ltd (NIWA)
> 301 Evans Bay Parade, Greta Point, Wellington
> Connect with NIWA: niwa.co.nz <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.niwa.co.nz&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&amp;sdata=s2gvY68GZi7OqTFz7YYZonbbJCmdBE9zVxlf%2FizJKZY%3D&amp;reserved=0>Facebook
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2Fnzniwa&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&amp;sdata=Euxh5tuy5xI7CaWR2wN3x3sD66w2VEP719AvMvrIm6E%3D&amp;reserved=0>Twitter
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fniwa_nz&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&amp;sdata=xspFw9heBoCTLqKKprbTixXKOeGDtoKz5LKNZgdodUk%3D&amp;reserved=0>LinkedIn
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fniwa&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&amp;sdata=Z1mta8714GDjOEK3%2FMwaBIAxvGL%2BhWRdrFENLx6CABg%3D&amp;reserved=0>Instagram
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Fniwa_science&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&amp;sdata=UxEdLhf2iDgWA0fQQryiFrMExBiBuWPvhwUBVQJ49C4%3D&amp;reserved=0>
>
> To ensure compliance with legal requirements and to maintain cyber
> security standards, NIWA's IT systems are subject to ongoing
> monitoring, activity logging and auditing. This monitoring and
> auditing service may be provided by third parties. Such third parties
> can access information transmitted to, processed by and stored on
> NIWA's IT systems.
>
>
>
>
>
>
> _______________________________________________
> mapserver-users mailing list
> [hidden email]
> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fmapserver-users&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&amp;sdata=Or5QlT2y7YnEoea9XqkSlgoyC1OAm0Q0BiklpHtZkZc%3D&amp;reserved=0


---
This email has been checked for viruses by Avast antivirus software.
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.avast.com%2Fantivirus&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&amp;sdata=ENQHGV2XiSeAnJaQv8ReCiQ45Z%2B4BtZIdNqI9zbokB0%3D&amp;reserved=0

_______________________________________________
mapserver-users mailing list
[hidden email]
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fmapserver-users&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&amp;sdata=Or5QlT2y7YnEoea9XqkSlgoyC1OAm0Q0BiklpHtZkZc%3D&amp;reserved=0
_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with failing Postgis query...

Brent Wood-7

Thanks guys,

I have solved this problem (but am working through another :-)


The logged SQL with the error says: "... find_srid('','rdb.species_master','point'))"... and it can't find the srid. This is because my query gets the point from the maps.null_point table, not the rdb.species_master one, so there is no geometry_columns entry for the column in the rdb table.

It seems mapserver uses the first table in the SQL from clause to find the SRID for the geometry column (even if that column in not in the first table), and my query had it as the second table listed... if I change the order of the tables in the from clause so the first one listed contains the geometry it seems to work...

Not sure if this is a bug? I can't find it mentioned anywhere on Google...


Cheers

Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI:  +64 (4) 3860529





Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
T +64-4-386-0529  
National Institute of Water & Atmospheric Research Ltd (NIWA)
301 Evans Bay Parade, Greta Point, Wellington
Connect with NIWA: niwa.co.nz Facebook Twitter LinkedIn Instagram
To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.

________________________________________
From: mapserver-users <[hidden email]> on behalf of Lime, Steve D (MNIT) <[hidden email]>
Sent: Wednesday, February 13, 2019 08:39
To: Stephen Woodbridge; [hidden email]
Subject: Re: [mapserver-users] Help with failing Postgis query...

Also, I'd turn on debugging. The PostGIS code is very good about writing meaningful debugging information to that log file.

-----Original Message-----
From: mapserver-users [mailto:[hidden email]] On Behalf Of Stephen Woodbridge
Sent: Sunday, February 10, 2019 11:04 PM
To: [hidden email]
Subject: Re: [mapserver-users] Help with failing Postgis query...

Brent,

Are you wrapping your query like:

DATA "select * from (select ROW_NUMBER() OVER (order by code) as id,
code, sci_name, com_name, lower(family_com)||' ('|| family_sci||')' as
Family, point from rdb.species_master s, maps.null_point p where code in
(select distinct species from trawl.t_catch) limit 10) as foo using
unique id using srid=4326"

where srid is appropriate for your data?

-Steve W

On 2/10/2019 7:51 PM, Brent Wood wrote:
>
> Hi,
>
>
> I'm getting an error with an SQL in a mapfile.
>
>
> The query works from from the command line:
>
>
> select ROW_NUMBER() OVER (order by code) as id, code, sci_name,
> com_name, lower(family_com)||' ('|| family_sci||')' as Family, point
> from rdb.species_master s, maps.null_point p where code in (select
> distinct species from trawl.t_catch) limit 10 ;
>
>
> id,code,sci_name,com_name,family,locn
> 1,AAT,Alainopasiphaea australis,Alainopasiphaea
> australis,,0101000020E610000000000000000000000000000000000000
>
> ...
>
>
> When embedded in a mapfile & invoked from a browser or QGIS I get an
> error:
>
>
> WFS server error. ms_error->code not found
> msPostGISLayerWhichShapes(): Query error. Error executing query.
>
>
>
> Any suggestions?
>
>
> Brent Wood
>
> Programme leader: Environmental Information Delivery
> NIWA
> DDI:  +64 (4) 3860529
>
>
> <https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.niwa.co.nz&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&amp;sdata=muvSp2QB5z9I7aenwMwa%2F5DmbW5M7wXVD%2BabvBeIS8A%3D&amp;reserved=0>
>
>
> Brent Wood
> Principal Technician - GIS and Spatial Data Management
> Programme Leader - Environmental Information Delivery
> T +64-4-386-0529
>
> National Institute of Water & Atmospheric Research Ltd (NIWA)
> 301 Evans Bay Parade, Greta Point, Wellington
> Connect with NIWA: niwa.co.nz <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.niwa.co.nz&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&amp;sdata=s2gvY68GZi7OqTFz7YYZonbbJCmdBE9zVxlf%2FizJKZY%3D&amp;reserved=0>Facebook
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2Fnzniwa&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&amp;sdata=Euxh5tuy5xI7CaWR2wN3x3sD66w2VEP719AvMvrIm6E%3D&amp;reserved=0>Twitter
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fniwa_nz&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&amp;sdata=xspFw9heBoCTLqKKprbTixXKOeGDtoKz5LKNZgdodUk%3D&amp;reserved=0>LinkedIn
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fniwa&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&amp;sdata=Z1mta8714GDjOEK3%2FMwaBIAxvGL%2BhWRdrFENLx6CABg%3D&amp;reserved=0>Instagram
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Fniwa_science&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&amp;sdata=UxEdLhf2iDgWA0fQQryiFrMExBiBuWPvhwUBVQJ49C4%3D&amp;reserved=0>
>
> To ensure compliance with legal requirements and to maintain cyber
> security standards, NIWA's IT systems are subject to ongoing
> monitoring, activity logging and auditing. This monitoring and
> auditing service may be provided by third parties. Such third parties
> can access information transmitted to, processed by and stored on
> NIWA's IT systems.
>
>
>
>
>
>
> _______________________________________________
> mapserver-users mailing list
> [hidden email]
> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fmapserver-users&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&amp;sdata=Or5QlT2y7YnEoea9XqkSlgoyC1OAm0Q0BiklpHtZkZc%3D&amp;reserved=0


---
This email has been checked for viruses by Avast antivirus software.
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.avast.com%2Fantivirus&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&amp;sdata=ENQHGV2XiSeAnJaQv8ReCiQ45Z%2B4BtZIdNqI9zbokB0%3D&amp;reserved=0

_______________________________________________
mapserver-users mailing list
[hidden email]
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fmapserver-users&amp;data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&amp;sdata=Or5QlT2y7YnEoea9XqkSlgoyC1OAm0Q0BiklpHtZkZc%3D&amp;reserved=0
_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users




_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users