[mapserver-users] Help with failing Postgis query...

Tue Feb 12 16:19:38 PST 2019

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

[cid:imagec1f72c.PNG at 56107fed.4c9e4abf]<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.

________________________________________
From: mapserver-users <mapserver-users-bounces at lists.osgeo.org> on behalf of Lime, Steve D (MNIT) <steve.lime at state.mn.us>
Sent: Wednesday, February 13, 2019 08:39
To: Stephen Woodbridge; mapserver-users at lists.osgeo.org
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:mapserver-users-bounces at lists.osgeo.org] On Behalf Of Stephen Woodbridge
Sent: Sunday, February 10, 2019 11:04 PM
To: mapserver-users at lists.osgeo.org
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&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&sdata=muvSp2QB5z9I7aenwMwa%2F5DmbW5M7wXVD%2BabvBeIS8A%3D&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&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&sdata=s2gvY68GZi7OqTFz7YYZonbbJCmdBE9zVxlf%2FizJKZY%3D&reserved=0>Facebook
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2Fnzniwa&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&sdata=Euxh5tuy5xI7CaWR2wN3x3sD66w2VEP719AvMvrIm6E%3D&reserved=0>Twitter
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fniwa_nz&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&sdata=xspFw9heBoCTLqKKprbTixXKOeGDtoKz5LKNZgdodUk%3D&reserved=0>LinkedIn
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fniwa&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543096664&sdata=Z1mta8714GDjOEK3%2FMwaBIAxvGL%2BhWRdrFENLx6CABg%3D&reserved=0>Instagram
> <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Fniwa_science&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&sdata=UxEdLhf2iDgWA0fQQryiFrMExBiBuWPvhwUBVQJ49C4%3D&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
> mapserver-users at lists.osgeo.org
> https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fmapserver-users&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&sdata=Or5QlT2y7YnEoea9XqkSlgoyC1OAm0Q0BiklpHtZkZc%3D&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&data=02%7C01%7Csteve.lime%40state.mn.us%7Ccd30802415dd452ec1eb08d68fde5c89%7Ceb14b04624c445198f26b89c2159828c%7C0%7C0%7C636854582543106672&sdata=ENQHGV2XiSeAnJaQv8ReCiQ45Z%2B4BtZIdNqI9zbokB0%3D&reserved=0

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20190213/06903a8f/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: imagec1f72c.PNG
Type: image/png
Size: 12288 bytes
Desc: imagec1f72c.PNG
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20190213/06903a8f/attachment-0001.png>