[pmapper-users] queries using two layers
Hello,
I have two PostGIS layers: one with lines representing hiking paths, one
with polygons representing administrative units, with the mapfile
definitions in [1].
I am trying to elaborate a query in pmapper which would allow to select
an admin unit in an options list and then in a second field select
amongst the hikes that cross through that admin unit.
To start with, one general question: is it possible to create an options
list dependent on the first options list (i.e. chose an admin unit in
the first list and the get a second options list with only those hikes
that cross that admin unit), or does the second field have to be a
suggest field ?
Now, even using a suggest field in the second part, I just can't seem to
get the correct config to make it work.
At [0] is the searchitem definition.
At this stage, I don't get any error message, but searches are empty,
even though executing them directly in PostgreSQL works.
No error message in the postgresql, php or apache2 logs...
Any hints ?
Moritz
[0] Searchitem definition
pgsql://USER:PASSWD@SERVER/DATABASE
SELECT DISTINCT muniname1,muniname1 FROM
communes ORDER BY muniname1
onchange="resetSuggestCache();$('#pmsfld_name').val('')"
pgsql://USER:PASSWD@SERVER/DATABASE
SELECT DISTINCT name,name FROM balades JOIN
communes ON (ST_Crosses(ST_Transform(balades.the_geom, 31370),
communes.the_geom)) WHERE name ~* E'^[search]' AND muniname1 =
'[dependfldval]' ORDER BY name
[1] Mapfile definitions
#admin units
LAYER
NAME 'communes'
TYPE POLYGON
TEMPLATE void
CONNECTIONTYPE postgis
CONNECTION "dbname='MyDatabase' host=localhost port=5432
user='MyUser' password='MyPassword' sslmode=disable"
DATA 'the_geom FROM (SELECT gid, the_geom, muniname1 FROM communes)
as subquery USING UNIQUE gid USING srid=31370'
METADATA
"DESCRIPTION" "communes"
"RESULT_FIELDS" "gid,muniname1"
"RESULT_HEADERS" "ID,Nom"
"ows_title" "communes"
"LAYER_ENCODING" "UTF-8"
END
TRANSPARENCY 100
PROJECTION
"init=epsg:31370"
END
CLASS
NAME 'communes'
STYLE
WIDTH 1
OUTLINECOLOR 0 0 0
END
END
END
#hikes
LAYER
NAME "balades"
TYPE LINE
TEMPLATE void
CONNECTIONTYPE postgis
CONNECTION "dbname='MyDatabase' host=localhost port=5432
user='MyUser' password='MyPassword' sslmode=disable"#Prendre simplement
tous les champs de la table balades
DATA "the_geom FROM (SELECT b.gid, b.the_geom, b.name,
b.duree_minutes, b.longueur_metres, upper(a.prenom) || ' ' ||
upper(a.nom) as nom, a.email, a.section FROM balades b JOIN auteur a ON
(b.id_auteur = a.id)) AS SUBQUERY USING UNIQUE gid USING srid=4326"
METADATA
"RESULT_FIELDS" "name,duree_minutes,longueur_metres,nom,section"
"RESULT_HEADERS" "BALADE,DUREE (min),LONGUEUR (m),AUTEUR,SECTION"
"DESCRIPTION" "Balades"
"ows_title" "balades"
"LAYER_ENCODING" "UTF-8"
END #Metadata
PROJECTION
"init=epsg:4326"
END #Projection
CLASS
NAME 'Balades'
COLOR 255 0 0
END #Class
END #Layer
--
Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
Get 100% visibility into your production application - at no cost.
Code-level diagnostics for performance bottlenecks with <2% overhead
Download for free and get started troubleshooting in minutes.
http://p.sf.net/sfu/appdyn_d2d_ap1
___
pmapper-users mailing list
pmapper-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/pmapper-users
Re: [pmapper-users] queries using two layers
On Mon, 06 May 2013 20:44:30 +0200, Armin Burger wrote:
> I would suggest you set the debugging level to a higher value like 3
> and
> then check the query logging in the debug log,
It already was set to level 3.
> see
>
>
> http://svn.pmapper.net/trac/wiki/FaqDebugging#a3.Enablep.mapperdebugging
>
> it should contain the queries sent to PG.
It doesn't. All I can find, is this:
PHP Notice: Undefined variable: dynamicClasses in
/home/geogf405/site_balades/incphp/layerview.php on line 218
Don't really know how to interpret that.
I did get see some PG queries before, so there must be something wrong
with my file. I'll try again tomorrow morning.
Thanks for the support and the great tool !
Moritz
>
> On 05/06/2013 05:34 PM, Moritz Lennert wrote:
>> Hello,
>>
>> I have two PostGIS layers: one with lines representing hiking paths,
>> one
>> with polygons representing administrative units, with the mapfile
>> definitions in [1].
>>
>> I am trying to elaborate a query in pmapper which would allow to
>> select
>> an admin unit in an options list and then in a second field select
>> amongst the hikes that cross through that admin unit.
>>
>> To start with, one general question: is it possible to create an
>> options
>> list dependent on the first options list (i.e. chose an admin unit
>> in
>> the first list and the get a second options list with only those
>> hikes
>> that cross that admin unit), or does the second field have to be a
>> suggest field ?
>>
>> Now, even using a suggest field in the second part, I just can't
>> seem to
>> get the correct config to make it work.
>>
>> At [0] is the searchitem definition.
>>
>> At this stage, I don't get any error message, but searches are
>> empty,
>> even though executing them directly in PostgreSQL works.
>>
>> No error message in the postgresql, php or apache2 logs...
>>
>> Any hints ?
>>
>> Moritz
>>
>>
>> [0] Searchitem definition
>>
>> > description="Choisir
>> une balade selon une commune bruxelloise">
>>
>>
>> > sort="asc" firstoption="Sélectionner une commune" nosubmit="1">
>>> encoding="UTF-8">pgsql://USER:PASSWD@SERVER/DATABASE
>>SELECT DISTINCT muniname1,muniname1 FROM
>> communes ORDER BY muniname1
>>
>>
>> onchange="resetSuggestCache();$('#pmsfld_name').val('')"
>>
>>
>>
>>
>> > minlength="1" sort="asc" firstoption="Sélectionner une balade"
>> dependfld="muniname1">
>>> encoding="UTF-8">pgsql://USER:PASSWD@SERVER/DATABASE
>>SELECT DISTINCT name,name FROM balades
>> JOIN
>> communes ON (ST_Crosses(ST_Transform(balades.the_geom, 31370),
>> communes.the_geom)) WHERE name ~* E'^[search]' AND muniname1 =
>> '[dependfldval]' ORDER BY name
>>
>>
>>
>>
>>
>>
>>
>>
>> [1] Mapfile definitions
>>
>> #admin units
>> LAYER
>> NAME 'communes'
>> TYPE POLYGON
>> TEMPLATE void
>> CONNECTIONTYPE postgis
>> CONNECTION "dbname='MyDatabase' host=localhost port=5432
>> user='MyUser' password='MyPassword' sslmode=disable"
>> DATA 'the_geom FROM (SELECT gid, the_geom, muniname1 FROM
>> communes)
>> as subquery USING UNIQUE gid USING srid=31370'
>> METADATA
>> "DESCRIPTION" "communes"
>> "RESULT_FIELDS" "gid,muniname1"
>> "RESULT_HEADERS" "ID,Nom"
>> "ows_title" "communes"
>> "LAYER_ENCODING" "UTF-8"
>> END
>> TRANSPARENCY 100
>> PROJECTION
>> "init=epsg:31370"
>> END
>> CLASS
>> NAME 'communes'
>> STYLE
>>WIDTH 1
>>OUTLINECOLOR 0 0 0
>> END
>> END
>> END
>>
>> #hikes
Re: [pmapper-users] queries using two layers
On 06/05/13 21:34, Moritz Lennert wrote: > On Mon, 06 May 2013 20:44:30 +0200, Armin Burger wrote: >> I would suggest you set the debugging level to a higher value like 3 >> and >> then check the query logging in the debug log, > > It already was set to level 3. > >> see >> >> >> http://svn.pmapper.net/trac/wiki/FaqDebugging#a3.Enablep.mapperdebugging >> >> it should contain the queries sent to PG. > > It doesn't. All I can find, is this in the php error log: > > PHP Notice: Undefined variable: dynamicClasses in > /home/geogf405/site_balades/incphp/layerview.php on line 218 > > Don't really know how to interpret that. > > I did get see some PG queries before, so there must be something wrong > with my file. I'll try again tomorrow morning. > Ok, reconfiguring error_log in php.ini (which was configured to send php errors to syslog), gives me a full pm_debug.log with more info. IIUC, the problem is that '[dependfldval]' is not substituted: SELECT DISTINCT name,name FROM balades JOIN communes ON (ST_Crosses(ST_Transform(balades.the_geom, 31370), communes.the_geom)) WHERE name ~* '^m' AND muniname1='[dependfldval]' ORDER BY name and obviously there is no admin unit that is called [dependfldval]. I also just noticed that my original post was incomplete: since I'm trying to combine two layers and I understood from the query examples that one cannot combine several layers in one searchitem, I created one layer in the mapfile combining my two vector layers from the PostGIS source (see below). The config query entry in the original post is based on that layer. So, my questions: - Is it correct to assume that I have to create a layer in the mapfile that combines the attributes from the two vector layers, or is it possible to combine two layers in one searchitem ? When I try to just include two definitions in one searchitem, only the field defined in the first layer definition appears on the screen. - Using the combined mapfile layer definition, why would dependfldval not be substituted ? relevant mapfile entry LAYER NAME "balades_avec_communes" TYPE LINE TEMPLATE void CONNECTIONTYPE postgis CONNECTION "dbname='MyDatabase' host=localhost port=5432 user='MyUser' password='MyPassword' sslmode=disable" DATA "the_geom FROM (SELECT b.gid, b.the_geom, b.name, b.duree_minutes, b.longueur_metres, upper(a.prenom) || ' ' || upper(a.nom) as nom, a.email, a.section, c.muniname1 FROM balades b JOIN auteur a ON (b.id_auteur = a.id) JOIN communes c ON (ST_Crosses(ST_Transform(b.the_geom, 31370), c.the_geom))) AS SUBQUERY USING UNIQUE gid USING srid=4326" METADATA "RESULT_FIELDS" "name,duree_minutes,longueur_metres,nom,section,muniname1" "RESULT_HEADERS" "BALADE,DUREE (min),LONGUEUR (m),AUTEUR,SECTION,COMMUNE" "DESCRIPTION" "Balades_Communes" "ows_title" "balades_communes" "LAYER_ENCODING" "UTF-8" END #Metadata PROJECTION "init=epsg:4326" END #Projection CLASS NAME 'Balades' COLOR 200 0 0 END #Class END #Layer * Moritz >> >> On 05/06/2013 05:34 PM, Moritz Lennert wrote: >>> Hello, >>> >>> I have two PostGIS layers: one with lines representing hiking paths, >>> one >>> with polygons representing administrative units, with the mapfile >>> definitions in [1]. >>> >>> I am trying to elaborate a query in pmapper which would allow to >>> select >>> an admin unit in an options list and then in a second field select >>> amongst the hikes that cross through that admin unit. >>> >>> To start with, one general question: is it possible to create an >>> options >>> list dependent on the first options list (i.e. chose an admin unit >>> in >>> the first list and the get a second options list with only those >>> hikes >>> that cross that admin unit), or does the second field have to be a >>> suggest field ? >>> >>> Now, even using a suggest field in the second part, I just can't >>> seem to >>> get the correct config to make it work. >>> >>> At [0] is the searchitem definition. >>> >>> At this stage, I don't get any error message, but searches are >>> empty, >>> even though executing them directly in PostgreSQL works. >>> >>> No error message in the postgresql, php or apache2 logs... >>> >>> Any h
