[mapserver-users] PostGIS query problem
Fernando Martins Pimenta
fernando.m.pimenta at gmail.com
Mon Jun 29 13:25:26 PDT 2020
When I run the query in pgadmin4 query tool it works correctly.
SELECT
hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS geom
FROM
vector.hidrography AS hidro,
vector.gcc AS gcc
WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name = UPPER('grande')
When I run the same query in mapfile it does not work (it returns only
null).
DATA "geom FROM (
SELECT
hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS geom
FROM
vector.hidrography AS hidro,
vector.gcc AS gcc
WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name = UPPER('grande')
) as subquery USING UNIQUE fid USING SRID=4326"
The difference is "as subquery USING UNIQUE fid USING SRID=4326"
When I run shp2image the query returns:
...
msPostGISReadShape: [shape] (null)
msPostGISReadShape called.
msPostGISReadShape: [shape] (null)
msPostGISReadShape called.
msPostGISReadShape: [shape] (null)
msPostGISReadShape called.
msPostGISReadShape: [shape] (null)
msPostGISReadShape called.
msPostGISReadShape: [shape] (null)
msPostGISReadShape called.
msPostGISReadShape: [shape] (null)
msPostGISLayerFreeItemInfo called.
msPostGISLayerClose called: geom FROM (
SELECT
hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS
geom
FROM
vector.hidrography AS hidro,
vector.gcc AS gcc
WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name =
UPPER('grande')
) as subquery USING UNIQUE fid USING SRID=4326
msConnPoolRelease(hidrografia,host=************** dbname=****** user=******
password=****** port=5432,0x55c19e119740)
msDrawMap(): Layer 0 (hidrografia), 163.260s
msDrawMap(): Drawing Label Cache, 0.000s
msDrawMap() total time: 163.260s
msSaveImage(ttt.png) total time: 0.011s
msFreeMap(): freeing map at 0x55c19e10d3f0.
freeLayer(): freeing layer at 0x55c19e114ae0.
msPostGISLayerIsOpen called.
shp2img total time: 163.272s
msConnPoolClose(host=************ dbname=******** user=********
password=******** port=5432,0x55c19e119740)
GDAL: In GDALDestroy - unloading GDAL shared library.
PostgreSQL log returns:
FATAL: connection with client has been lost
COMMAND: select ST_AsBinary (("geom"), 'NDR') as geom, "fid" :: text from (
SELECT
hydro.fid, hydro.name, ST_Intersection (gcc.geom, hydro.geom) AS geom
FROM
vector.hidrography AS hydro,
vector.gcc AS gcc
WHERE ST_Intersects (gcc.geom, hydro.geom) AND gcc.name = UPPER ('grande'))
as subquery where "geom" && ST_GeomFromText ('POLYGON ((- 46.7031998460824
-15.4471601311111, -46.703199856608-9353563593463563593563561035610610356))
, -43.0600566044731 -15.4471601311111, -46.7031998460824
-15.4471601311111)) ', 4326)
I didn't find out why the mapserver is not executing this query correctly.
*Fernando Martins Pimenta* <http://lattes.cnpq.br/0646984654461300>
Graduando em Engenharia de Agrimensura e Cartográfica - UFV
Bacharel em Engenharia de Biossistemas - UFSJ
www.biosfera.dea.ufv.br
On Fri, Jun 26, 2020 at 1:02 PM <mapserver-users-request at lists.osgeo.org>
wrote:
> Send mapserver-users mailing list submissions to
> mapserver-users at lists.osgeo.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
> or, via email, send a message with subject or body 'help' to
> mapserver-users-request at lists.osgeo.org
>
> You can reach the person managing the list at
> mapserver-users-owner at lists.osgeo.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of mapserver-users digest..."
>
>
> Today's Topics:
>
> 1. PostGIS query problem (Fernando Martins Pimenta)
> 2. Re: PostGIS query problem (Seth G)
> 3. Re: PostGIS query problem (Jeff McKenna)
> 4. Sliver when displaying 0°-360° NetCDF data in EPSG:3857
> (Rousseau Lambert2, Louis-Philippe (EC))
> 5. Problem with Python MapScript queryByRect (Just van den Broecke)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Thu, 25 Jun 2020 17:16:33 -0300
> From: Fernando Martins Pimenta <fernando.m.pimenta at gmail.com>
> To: mapserver-users at lists.osgeo.org
> Subject: [mapserver-users] PostGIS query problem
> Message-ID:
> <
> CA+VvBcEZ5tUetUXjAc-DSt3NMLo_ZCTX2oP4yiiEd0VUrX-odQ at mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Hi,
>
> I am using the query below to return a hydrograph of a region (using
> POSTGIS). This query works perfectly when I use it in pgadmin. With the
> mapserver, the connection with the client is lost. It records a query that
> shows no errors in SQL, just shows that the connection has lost. Why is
> that?
>
> DATA "geom FROM (
> SELECT
> hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom)
> AS
> geom
> FROM
> vector.hidrography AS hidro,
> vector.gcc AS gcc
> WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name =
> 'GRANDE'
> ) as subquery USING UNIQUE fid USING SRID=4326"
>
> Thanks in advance
>
> *Fernando Martins Pimenta <http://lattes.cnpq.br/0646984654461300>*
> Graduando em Engenharia de Agrimensura e Cartográfica - UFV
> Bacharel em Engenharia de Biossistemas - UFSJ
>
> www.biosfera.dea.ufv.br
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/mapserver-users/attachments/20200625/e300a5d6/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 2
> Date: Thu, 25 Jun 2020 22:20:50 +0200
> From: "Seth G" <sethg at geographika.co.uk>
> To: mapserver-users at lists.osgeo.org
> Subject: Re: [mapserver-users] PostGIS query problem
> Message-ID: <e0622663-7609-424b-9cc5-aac53925fd05 at www.fastmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Hi,
>
> What client are you using?
> How long does the query take to complete?
> It is likely the client stops waiting for a response.
>
> Seth
>
> --
> web:http://geographika.co.uk
> twitter: @geographika
>
>
> On Thu, Jun 25, 2020, at 10:16 PM, Fernando Martins Pimenta wrote:
> > Hi,
> >
> > I am using the query below to return a hydrograph of a region (using
> POSTGIS). This query works perfectly when I use it in pgadmin. With the
> mapserver, the connection with the client is lost. It records a query that
> shows no errors in SQL, just shows that the connection has lost. Why is
> that?
> >
> > DATA "geom FROM (
> > SELECT
> > hidro.fid, hidro.name, ST_Intersection(gcc.geom, hidro.geom) AS geom
> > FROM
> > vector.hidrography AS hidro,
> > vector.gcc AS gcc
> > WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name = 'GRANDE'
> > ) as subquery USING UNIQUE fid USING SRID=4326"
> >
> > Thanks in advance
> >
> > *Fernando Martins Pimenta <http://lattes.cnpq.br/0646984654461300>*
> > Graduando em Engenharia de Agrimensura e Cartográfica - UFV
> > Bacharel em Engenharia de Biossistemas - UFSJ
> >
> > www.biosfera.dea.ufv.br
> >
> > _______________________________________________
> > 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/20200625/af1484e5/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 3
> Date: Thu, 25 Jun 2020 21:36:58 -0300
> From: Jeff McKenna <jmckenna at gatewaygeomatics.com>
> To: mapserver-users at lists.osgeo.org
> Subject: Re: [mapserver-users] PostGIS query problem
> Message-ID:
> <c2612680-d503-215e-14a7-fe4bac21c4a4 at gatewaygeomatics.com>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> Hi Fernando,
>
> I would try using the shp2img utility at the commandline, and set CONFIG
> "CPL_DEBUG" "ON" in your mapfile (see
>
> https://mapserver.org/optimization/debugging.html#step-3-turn-on-cpl-debug-optional)
>
> and then execute:
>
> shp2img -m mymap.map -o ttt.png -all_debug 5
>
> The full query should be listed there in the response, which you can
> then paste into the psql commandline, and manually execute the same
> query to your database.
>
> PS. hello to my friends in Brasil!
>
> -jeff
>
>
>
> --
> Jeff McKenna
> MapServer Consulting and Training Services
> co-founder of FOSS4G
> http://gatewaygeo.com/
>
>
> On 2020-06-25 5:16 p.m., Fernando Martins Pimenta wrote:
> > Hi,
> >
> > I am using the query below to return a hydrograph of a region (using
> > POSTGIS). This query works perfectly when I use it in pgadmin. With the
> > mapserver, the connection with the client is lost. It records a query
> > that shows no errors in SQL, just shows that the connection has lost.
> > Why is that?
> >
> > DATA "geom FROM (
> > SELECT
> > hidro.fid, hidro.name <http://hidro.name>,
> > ST_Intersection(gcc.geom, hidro.geom) AS geom
> > FROM
> > vector.hidrography AS hidro,
> > vector.gcc AS gcc
> > WHERE ST_Intersects(gcc.geom, hidro.geom) AND gcc.name
> > <http://gcc.name> = 'GRANDE'
> > ) as subquery USING UNIQUE fid USING SRID=4326"
> >
> > Thanks in advance
> >
> > *Fernando Martins Pimenta <http://lattes.cnpq.br/0646984654461300>*
> > Graduando em Engenharia de Agrimensura e Cartográfica - UFV
> > Bacharel em Engenharia de Biossistemas - UFSJ
> >
> > www.biosfera.dea.ufv.br <http://www.biosfera.dea.ufv.br>
> >
> >
> >
>
>
> ------------------------------
>
> Message: 4
> Date: Fri, 26 Jun 2020 13:00:31 +0000
> From: "Rousseau Lambert2, Louis-Philippe (EC)"
> <louis-philippe.rousseaulambert2 at canada.ca>
> To: "mapserver-users at lists.osgeo.org"
> <mapserver-users at lists.osgeo.org>
> Subject: [mapserver-users] Sliver when displaying 0°-360° NetCDF
> data in EPSG:3857
> Message-ID:
> <mailman.97986.1593187336.17185.mapserver-users at lists.osgeo.org>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Hi,
>
> This issue is related to:
>
> *
> https://github.com/mapserver/mapserver/commit/722716c49de2399f54b275ab31437f0e8c92cd63
> *
> https://github.com/mapserver/mapserver/commit/4c2aeb4bbd229723d8ea67c337cf02350575b2ae
>
> The issue is that when I try to visualize (via WMS) a layer with a NetCDF
> data file with longitude from 0° to 360° in EPSG:3857, there is a sliver
> around longitude 0° where no data is displayed.
>
> We already had that issue with the same files using EPSG:4326 and it's
> been fixed in MapServer (see merge request above).
>
> Here is where you can find example files:
> https://dd.weather.gc.ca/model_giops/netcdf/lat_lon/2d/00/003/CMC_giops_votemper_depth_0.5_latlon0.2x0.2_3h-mean_{yyyymmdd}00_P003.nc
>
> We are testing using: MapServer version 7.4.4 OUTPUT=PNG OUTPUT=JPEG
> OUTPUT=KML SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO
> SUPPORTS=SVG_SYMBOLS SUPPORTS=RSVG SUPPORTS=ICONV SUPPORTS=FRIBIDI
> SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER
> SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER
> SUPPORTS=FASTCGI SUPPORTS=THREADS SUPPORTS=GEOS SUPPORTS=POINT_Z_M
> INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE
>
> Here is a minimal mapfile to reproduce:
>
> MAP
> NAME ""
> IMAGETYPE PNG
> EXTENT -180 -90 180 90
> MAXSIZE 4096
> SIZE 500 300
> IMAGECOLOR 255 255 255
> PROJECTION
> "init=epsg:4326"
> END
> TRANSPARENT ON
> DEBUG 5
> WEB
> METADATA
> "ows_extent" "-180 -90 180 90"
> "wms_getmap_formatlist" "image/png,image/jpeg"
> "wms_timeformat" "YYYY-MM-DDTHH:MM:SSZ"
> "wms_getfeatureinfo_formatlist" "text/plain"
> "ows_enable_request" "*"
> "ows_abstract" ""
> "ows_srs" "EPSG:4326 EPSG:3857"
> "ows_title" ""
> END
> END
> LAYER
> NAME "TEST"
> DEBUG 5
> TYPE RASTER
> PROCESSING "GDAL_NETCDF_BOTTOMUP=YES"
> TOLERANCE 15
> TEMPLATE "ttt.html"
> PROJECTION
> "proj=longlat"
> "a=6371229"
> "b=6371229"
> "lon_wrap=180"
> "no_defs"
> END
> DATA ''
> METADATA
> "ows_title" ""
> "ows_abstract" ""
> "ows_extent" "-180 -80.1 180 89.9"
> "ows_geomtype" "Geometry"
> END
> CLASS
> NAME ""
> STYLE
> COLORRANGE 0 0 127 127 0 0
> DATARANGE 200.00 400
> END
> END
> END
> END
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <
> http://lists.osgeo.org/pipermail/mapserver-users/attachments/20200626/09ce9091/attachment.html
> >
>
> ------------------------------
>
> Message: 5
> Date: Fri, 26 Jun 2020 17:55:01 +0200
> From: Just van den Broecke <justb4 at gmail.com>
> To: mapserver-users at lists.osgeo.org
> Subject: [mapserver-users] Problem with Python MapScript queryByRect
> Message-ID: <35fd8e93-e9c2-240b-154f-4c231c4c0d00 at gmail.com>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> Hi,
>
> I am reviving a Python 2 MapServer MapScript app that used to work 7
> years ago (and still is in old deployment) but stuck in some MapScript
> calls that fail but also throw no Exceptions.
>
> Purpose of the app "MapGlow" [1] is to generate a WMS-heatmap triggered
> by specific STYLE parameters. When no heatmap STYLE is requested
> requests are delegated to map_file.OWSDispatch() etc. The latter works
> fine so the overall setup/stack/mapfile is working. Using MS 7.6.0 in
> Docker (compose) with Lighttp, FastCGI, WSGI, flup.
>
> It is only when layerObj.queryByRect() is called, a HTTP 500 is
> returned after a FastCGI hard error, no means to catch an Exception. The
> passed MapFile and Rect objects seem ok. Also I noticed that
> layerObj.metadata had 0 items. I can see that I have proper SWIG objects
> like
> <mapscript.layerObj; proxy of <Swig Object of type 'layerObj *' at
> 0x7fded06b1de0> >
> If only I could get a hint, a MapFile setting, maybe some log output or
> debug flag I can set? The WIP is on GH, I can point at the failing line
> [2] but the code is
> quite involved (mind, my first Python 7-9 years ago):
>
> [1] https://github.com/justb4/mapglow
> [2]
> https://github.com/justb4/mapglow/blob/master/app1/wms/mapglow_ms.py#L249
>
> Thanks for any help,
>
> Just van den Broecke
> The Netherlands
>
>
> ------------------------------
>
> Subject: Digest Footer
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>
> ------------------------------
>
> End of mapserver-users Digest, Vol 149, Issue 15
> ************************************************
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20200629/37535161/attachment-0001.html>
More information about the mapserver-users
mailing list