Re: [mapserver-users] PostGIS query problem

[Carlos Ruiz]

Hola, Fernando,
I don't know what's really going on but despite if this is a GDAL error or 
something else, I suggest you to try the following in the MAP file LAYER 
section:

PROCESSING "CLOSE_CONNECTION=DEFER"
Another question: does PostgreSQL server is running in another machine ?

MSc. Carlos Ruiz ___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] PostGIS query problem

[Fernando Martins Pimenta]

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* <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 
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
>  

Re: [mapserver-users] PostGIS query problem

[Jeff McKenna]

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 , 
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 *
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@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] PostGIS query problem

[Seth G]

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 *
> 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@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

[mapserver-users] PostGIS query problem

[Fernando Martins Pimenta]

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 *
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@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] PostGIS query errors

[Kralidis, Tom (EC)]

> -Original Message-
> From: Andy Colson [mailto:a...@squeakycode.net]
> Sent: 09 November 2016 17:43
> To: Kralidis, Tom (EC); mapserver-users@lists.osgeo.org
> Subject: Re: [mapserver-users] PostGIS query errors
> 
> On 11/9/2016 3:30 PM, Kralidis, Tom (EC) wrote:
> > Hi all: using 7.0.2 against a PostgreSQL 9.3/PostGIS 2.2 instance we're 
> > getting
> query errors.
> >
> > I've posted a Gist at [1] to help in reporting.  Note that this 
> > functionality used
> to work in 6.4.x.
> >
> > From the migration guides it doesn't look like much has changed w.r.t.
> > PostGIS syntax in the mapfile.  Any idea what could be going on here?
> > It looks like the query that is constructed by mappostgis.c is something 
> > like:
> >
> > select
> >
> "stn_nam","avg_wnd_dir_10m_mt58_60","avg_wnd_spd_10m_mt58_60_symb
> ol","
> > air_temp","stn_pres","mslp","dwpt_temp","rel_hum","observation_qa_summ
> >
> ary","observation_qa_problem_summary",encode(ST_AsBinary(ST_Force2D("t
> > he_geom"),'NDR'),'hex') as geom,"data_payload_id" from (select * from
> > data_mart_ca_live order by instance_datetime desc) as subquery where
> > the_geom && ST_GeomFromText('POLYGON((-127.881959628204
> > 63.7547758989546,-127.881959628204 70.5833900726923,-
> 103.751686743445
> > 70.5833900726923,-103.751686743445 63.7547758989546,-
> 127.881959628204
> > 63.7547758989546))',4326) and ()
> >
> > Note the trailing "()".  When I remove the "()" and try direct in psql, the 
> > query
> works.
> >
> > Any idea what could be going on here?
> >
> > Thanks
> >
> > ..Tom
> >
> > [1]
> > https://gist.github.com/tomkralidis/26cb7214e282c144ba61598116d458e7
> > ___
> > mapserver-users mailing list
> > mapserver-users@lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/mapserver-users
> >
> 
> It looks to me, from the code mappostgis.c ~line 2018, in the
> msPostGISBuildSQLWhere function, that you have either a FILTER or
> NATIVE_FILTER being added.  I don't see it in the gist you posted though.  Is 
> it
> applied later some how?  I'd bet it's also empty.
> 

Indeed, it turns out that Python MapScript was doing a setFilter as part of the
workflow (since changed to setProcessingKey).

Thanks

..Tom


> There is a comment about RFC91 too:
> http://mapserver.org/id/development/rfc/ms-rfc-91.html
> 
> any of that apply to you?
> 
> -Andy
> 

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] PostGIS query errors

[Andy Colson]

On 11/9/2016 3:30 PM, Kralidis, Tom (EC) wrote:

Hi all: using 7.0.2 against a PostgreSQL 9.3/PostGIS 2.2 instance we're getting 
query errors.

I've posted a Gist at [1] to help in reporting.  Note that this functionality 
used to work in 6.4.x.

From the migration guides it doesn't look like much has changed w.r.t. PostGIS 
syntax in
the mapfile.  Any idea what could be going on here?  It looks like the query 
that is constructed
by mappostgis.c is something like:

select 
"stn_nam","avg_wnd_dir_10m_mt58_60","avg_wnd_spd_10m_mt58_60_symbol","air_temp","stn_pres","mslp","dwpt_temp","rel_hum","observation_qa_summary","observation_qa_problem_summary",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'hex')
 as geom,"data_payload_id" from (select * from data_mart_ca_live order by instance_datetime desc) as subquery where the_geom && ST_GeomFromText('POLYGON((-127.881959628204 
63.7547758989546,-127.881959628204 70.5833900726923,-103.751686743445 70.5833900726923,-103.751686743445 63.7547758989546,-127.881959628204 63.7547758989546))',4326) and ()

Note the trailing "()".  When I remove the "()" and try direct in psql, the 
query works.

Any idea what could be going on here?

Thanks

..Tom

[1] https://gist.github.com/tomkralidis/26cb7214e282c144ba61598116d458e7
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users



It looks to me, from the code mappostgis.c ~line 2018, in the 
msPostGISBuildSQLWhere function, that you have either a FILTER or 
NATIVE_FILTER being added.  I don't see it in the gist you posted 
though.  Is it applied later some how?  I'd bet it's also empty.


There is a comment about RFC91 too:
http://mapserver.org/id/development/rfc/ms-rfc-91.html

any of that apply to you?

-Andy


___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

[mapserver-users] PostGIS query errors

[Kralidis, Tom (EC)]

Hi all: using 7.0.2 against a PostgreSQL 9.3/PostGIS 2.2 instance we're getting 
query errors.

I've posted a Gist at [1] to help in reporting.  Note that this functionality 
used to work in 6.4.x.

From the migration guides it doesn't look like much has changed w.r.t. PostGIS 
syntax in
the mapfile.  Any idea what could be going on here?  It looks like the query 
that is constructed
by mappostgis.c is something like:

select 
"stn_nam","avg_wnd_dir_10m_mt58_60","avg_wnd_spd_10m_mt58_60_symbol","air_temp","stn_pres","mslp","dwpt_temp","rel_hum","observation_qa_summary","observation_qa_problem_summary",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'hex')
 as geom,"data_payload_id" from (select * from data_mart_ca_live order by 
instance_datetime desc) as subquery where the_geom && 
ST_GeomFromText('POLYGON((-127.881959628204 63.7547758989546,-127.881959628204 
70.5833900726923,-103.751686743445 70.5833900726923,-103.751686743445 
63.7547758989546,-127.881959628204 63.7547758989546))',4326) and ()

Note the trailing "()".  When I remove the "()" and try direct in psql, the 
query works.

Any idea what could be going on here?

Thanks

..Tom

[1] https://gist.github.com/tomkralidis/26cb7214e282c144ba61598116d458e7
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] PostGIS query

[Paul Ramsey]

http://postgis.org/documentation/manual-1.5/ch05.html#id2634590

On Tue, Jan 31, 2012 at 4:38 AM, Julien Cigar jci...@ulb.ac.be wrote:
 Hello,

 Is there a way to (deeply) modify the SQL query in a PostGIS layer?
 I would like to add some JOIN, additional WHERE clauses, etc
 As I'm afraid that Run-time Substitution isn't flexible enough for this
 case ... is there any other possibility, other than mapscript?

 I guess there is no possibility to define variables, conditions, etc in a
 .map file .. ?

 Thanks,
 Julien

 --
 No trees were killed in the creation of this message.
 However, many electrons were terribly inconvenienced.

 ___
 mapserver-users mailing list
 mapserver-users@lists.osgeo.org
 http://lists.osgeo.org/mailman/listinfo/mapserver-users

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] PostGIS query

[Carlos Ruiz]

Hi Julien,


 I usually use a view, but in this case there are a lot of tables 
 involved and I would like to avoid dozens of unnecessary JOIN


I think that you have a database design problem.

You can have a lot of tables which concerns to a different objects, but you 
need to show some information on a map which involves many of those tables. 
That's why you have to create different views or in the worst case, a super 
view as a spreadsheet.

I guess that's why you're talking about unnecessary JOINs, but in the end, 
you'll need to use them to create the multiple data sources or the super data 
source of your layers in the .MAP file.



IC Carlos Ruiz




 From: Julien Cigar jci...@ulb.ac.be
To: Carlos Ruiz boolean10...@yahoo.com 
Cc: mapserver-users@lists.osgeo.org mapserver-users@lists.osgeo.org 
Sent: Wednesday, February 1, 2012 2:59 AM
Subject: Re: [mapserver-users] PostGIS query
 
On 01/31/2012 17:05, Carlos Ruiz wrote:
 Julien,

Hi Carlos,


 Have you tried with views ? When I need to map complex queries I build a
 view to just handle the where clauses.


I usually use a view, but in this case there are a lot of tables 
involved and I would like to avoid dozens of unnecessary JOIN

 Cheers from México
 IC Carlos Ruiz

 
 *From:* Julien Cigar jci...@ulb.ac.be
 *To:* mapserver-users@lists.osgeo.org mapserver-users@lists.osgeo.org
 *Sent:* Tuesday, January 31, 2012 6:38 AM
 *Subject:* [mapserver-users] PostGIS query

 Hello,

 Is there a way to (deeply) modify the SQL query in a PostGIS layer?
 I would like to add some JOIN, additional WHERE clauses, etc
 As I'm afraid that Run-time Substitution isn't flexible enough for
 this case ... is there any other possibility, other than mapscript?

 I guess there is no possibility to define variables, conditions, etc in
 a .map file .. ?

 Thanks,
 Julien

 -- No trees were killed in the creation of this message.
 However, many electrons were terribly inconvenienced.

 ___
 mapserver-users mailing list
 mapserver-users@lists.osgeo.org mailto:mapserver-users@lists.osgeo.org
 http://lists.osgeo.org/mailman/listinfo/mapserver-users




-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] PostGIS query

[Julien Cigar]

On 01/31/2012 17:05, Carlos Ruiz wrote:

Julien,


Hi Carlos,



Have you tried with views ? When I need to map complex queries I build a
view to just handle the where clauses.



I usually use a view, but in this case there are a lot of tables 
involved and I would like to avoid dozens of unnecessary JOIN



Cheers from México
IC Carlos Ruiz


*From:* Julien Cigar jci...@ulb.ac.be
*To:* mapserver-users@lists.osgeo.org mapserver-users@lists.osgeo.org
*Sent:* Tuesday, January 31, 2012 6:38 AM
*Subject:* [mapserver-users] PostGIS query

Hello,

Is there a way to (deeply) modify the SQL query in a PostGIS layer?
I would like to add some JOIN, additional WHERE clauses, etc
As I'm afraid that Run-time Substitution isn't flexible enough for
this case ... is there any other possibility, other than mapscript?

I guess there is no possibility to define variables, conditions, etc in
a .map file .. ?

Thanks,
Julien

-- No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org mailto:mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users





--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
attachment: jcigar.vcf___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

[mapserver-users] PostGIS query

[Julien Cigar]

Hello,

Is there a way to (deeply) modify the SQL query in a PostGIS layer?
I would like to add some JOIN, additional WHERE clauses, etc
As I'm afraid that Run-time Substitution isn't flexible enough for 
this case ... is there any other possibility, other than mapscript?


I guess there is no possibility to define variables, conditions, etc in 
a .map file .. ?


Thanks,
Julien

--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
attachment: jcigar.vcf___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] PostGIS query

[Carlos Ruiz]

Julien,

Have you tried with views ? When I need to map complex queries I build a view 
to just handle the where clauses.

Cheers from México

 
IC Carlos Ruiz




 From: Julien Cigar jci...@ulb.ac.be
To: mapserver-users@lists.osgeo.org mapserver-users@lists.osgeo.org 
Sent: Tuesday, January 31, 2012 6:38 AM
Subject: [mapserver-users] PostGIS query
 
Hello,

Is there a way to (deeply) modify the SQL query in a PostGIS layer?
I would like to add some JOIN, additional WHERE clauses, etc
As I'm afraid that Run-time Substitution isn't flexible enough for this case 
... is there any other possibility, other than mapscript?

I guess there is no possibility to define variables, conditions, etc in a .map 
file .. ?

Thanks,
Julien

-- No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

[mapserver-users] Postgis query bug?

[pcreso]

Hi,

I can't get an error with teh sql via psql, but only mapserver, so I'll ask 
here.

I'm using a mapfile with a DATA statement similar to the ones at:
http://mapserver.org/input/vector/postgis.html

The data statement is below...

The random() field, combined with order by  limit is to generate a random 
subset of records from each query.   

I can order by a genuine int field  get the expected result in mapserver 
(WFS) response. I can order by a calculated field 'random() as rand' and get 
the expected random sorted list back. I can impose a limit 5 on the fixed order 
by field,  get the 5 records back. All work with the SQL in the psql 
commandline  the mapfile.

However, if I try to order by rand then limit 5, it works fine from the command 
line, but returns no records/features via WFS/mapserver. 

So, with teh layer below, if I change the field to order by to atlas_id (an int 
in the view being queried) it works. If I leave the order by as rand (random() 
as rand in the view being queried) with no limit 5 I get a valid response 
with some 250 records via WFS, if I use limit n with the datasets ordered by 
rand, I get zero records returned via WFS, but the metadata shows the data 
extent to be correct.

Any advice appreciated...

Brent Wood

DATA startp from ( select atlas_id, 
 filename,
 startp,
 rand
  from
   t_station t,
   V_atlas_id a
  where t.trip_code = 'tan0906'
and t.station_no = 241
and t.station_no=a.station_no
and t.trip_code=a.trip_code 
  order by rand
  limit 5
   ) as myquery
   using unique atlas_id using srid=4326
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

[mapserver-users] Postgis query

[mark balman]

Hi All

Many thanks to Paul with regards to enabling statement logging
(extremely useful to help find out what sql is being sent) and thanks
to Carlos for suggesting the joins.. this was the problem which I can
happily say that I have resolved. What an amazing combination of
software really, Mapserver and Postgis are great products..

Cheers to all

Mark
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Postgis query

[Paul Ramsey]

If you turn on statement logging in pgsql, you can see what SQL
mapserver is sending to pgsql. That will clarify if there is a way for
you to change your statement to get what you want, or if there's an
error in what mapserver is asking for, or an error in how mapserver is
processing what is returned to it.

P

On Tue, Dec 2, 2008 at 2:37 AM, mark balman [EMAIL PROTECTED] wrote:
 Hi All

 I am trying to output a query from postgis using three tables and it
 is not quite working.

 First table is a quarter degree grid (spatial table)
 Second table is a table with each grid cell id along with many species
 id per grid cell
 Third table is list of species

 My query definition works well as the following (using two tables only)

 select fid_1, the_geom AS the_geom, spcgrid.id as gid, spcid
 from spcdensity
 left join spcgrid on spcdensity.fid_1 = spcgrid.fid

 This produces exactly what I want through mapserver, query a grid cell
 and it returns a list of species within that grid cell

 My problem is that I want to return a list of species names so my
 query at present is defined as:

 select spcid, spccommonname, fid_1, spcdensity.the_geom, spcid as gid
 from spcgrid left join species on spcgrid.spcid = species.spcrecid
 inner join spcdensity on spcdensity.fid_1 = spcgrid.fid

 Although the query works when run in pgadmin, when I query a grid cell
 I get a list of one species repeated. I am slightly confused as to
 what I am missing here, can anyone provide me with some pointers?

 Thanks in advance

 Mark
 ___
 mapserver-users mailing list
 mapserver-users@lists.osgeo.org
 http://lists.osgeo.org/mailman/listinfo/mapserver-users

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Postgis query

[Carlos Ruiz]

I guess the problem resides in the use of INNER and LEFT joins. Also, the order 
of the 
tables is important. The first tables specified in a query within the 
joins must be the ones 
which stablish the records agrupation or discrimination.

IC Carlos Ruiz
 

--- On Tue, 12/2/08, mark balman [EMAIL PROTECTED] wrote:

From: mark balman [EMAIL PROTECTED]
Subject: [mapserver-users] Postgis query
To: mapserver-users@lists.osgeo.org
Date: Tuesday, December 2, 2008, 4:37 AM

Hi All

I am trying to output a query from postgis using three tables and it
is not quite working.

First table is a quarter degree grid (spatial table)
Second table is a table with each grid cell id along with many species
id per grid cell
Third table is list of species

My query definition works well as the following (using two tables only)

select fid_1, the_geom AS the_geom, spcgrid.id as gid, spcid
from spcdensity
left join spcgrid on spcdensity.fid_1 = spcgrid.fid

This produces exactly what I want through mapserver, query a grid cell
and it returns a list of species within that grid cell

My problem is that I want to return a list of species names so my
query at present is defined as:

select spcid, spccommonname, fid_1, spcdensity.the_geom, spcid as gid
from spcgrid left join species on spcgrid.spcid = species.spcrecid
inner join spcdensity on spcdensity.fid_1 = spcgrid.fid

Although the query works when run in pgadmin, when I query a grid cell
I get a list of one species repeated. I am slightly confused as to
what I am missing here, can anyone provide me with some pointers?

Thanks in advance

Mark
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users



  ___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

[mapserver-users] Postgis query

[mark balman]

Hi All

I am trying to output a query from postgis using three tables and it
is not quite working.

First table is a quarter degree grid (spatial table)
Second table is a table with each grid cell id along with many species
id per grid cell
Third table is list of species

My query definition works well as the following (using two tables only)

select fid_1, the_geom AS the_geom, spcgrid.id as gid, spcid
from spcdensity
left join spcgrid on spcdensity.fid_1 = spcgrid.fid

This produces exactly what I want through mapserver, query a grid cell
and it returns a list of species within that grid cell

My problem is that I want to return a list of species names so my
query at present is defined as:

select spcid, spccommonname, fid_1, spcdensity.the_geom, spcid as gid
from spcgrid left join species on spcgrid.spcid = species.spcrecid
inner join spcdensity on spcdensity.fid_1 = spcgrid.fid

Although the query works when run in pgadmin, when I query a grid cell
I get a list of one species repeated. I am slightly confused as to
what I am missing here, can anyone provide me with some pointers?

Thanks in advance

Mark
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users