[mapserver-users] 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: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