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.447160131, -46.703199856608-9353563593463563593563561035610610356))
, -43.0600566044731 -15.447160131, -46.7031998460824
-15.447160131)) ', 4326)
I didn't find out why the mapserver is not executing this query correctly.
*Fernando Martins Pimenta*
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
wrote:
> Send mapserver-users mailing list submissions to
> mapserver-users@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-requ...@lists.osgeo.org
>
> You can reach the person managing the list at
> mapserver-users-ow...@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
> To: mapserver-users@lists.osgeo.org
> Subject: [mapserver-users] PostGIS query problem
> Message-ID:
> <
> ca+vvbcez5tuetuxjac-dst3nmlo_zctx2op4yiied0vurx-...@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
