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:[email protected]]<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 <[email protected]> on behalf of
Lime, Steve D (MNIT) <[email protected]>
Sent: Wednesday, February 13, 2019 08:39
To: Stephen Woodbridge; [email protected]
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:[email protected]] On
Behalf Of Stephen Woodbridge
Sent: Sunday, February 10, 2019 11:04 PM
To: [email protected]
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
> [email protected]
> 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
[email protected]
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
[email protected]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
_______________________________________________
mapserver-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
