Re: [pmapper-users] join to postgresql table problem
Hi, Moreover you can use a long query to execute (or a view) in the LAYER->DATA parameter. And then specify only the geometric table (it requires all fields that are used for drawing : attributes binding, label, ...) in LAYER->METADATA->PM_RESULT_DATASUBSTITUTION. This one will be used for drawing, graphical queries, etc... by replacing on the fly the DATA string. Whereas the "normal" DATA string will be used to retrieve attributes values for selections results, attribute queries, ... Thomas Le 26/04/2013 09:26, Suman a écrit : > hello Valerio, > > sorry for late reply, if you r still searching the answer and anybody > searching for this answer. then here is solution. > > As you asked for to display joined records from external table in query > identify div window, you need to query from DATA tag in map file. like this. > > LAYER >NAME "Building Footprint" >TYPE polygon >CONNECTIONTYPE postgis > CONNECTION "dbname=databasename host=localhost port=5432 user=postgres > password=xxx" > DATA "geom FROM (SELECT a.geom as geom, a.gid as gid, b.tole as tole_b1, > b.houseid as houseid, b.photono as photono FROM tbl_building a, > tbl_socio_economic b WHERE a.join_id = b.join_id) AS building_socio USING > UNIQUE gid" >TRANSPARENCY 30 >PROCESSING "CLOSE_CONNECTION=DEFER" >TEMPLATE void > >METADATA > "DESCRIPTION" "Building Footprint" > ## ADAPTED TO MULTILINGUAL SHAPEFILE > "RESULT_FIELDS" "tole_b1,houseid,photono,area,funct_use" > "RESULT_HEADERS" "tole_b1,houseid,photono,AREA,funct_use" > "ows_title" "Ward" >END # Metadata > >CLASS > Name 'All' > COLOR 223 219 212 > OUTLINECOLOR 223 219 212 >END # Class > END # Layer > > > And i am using pmapper 4.2.0... > > and you can query as more field as you can specifying in DATA query. > > cheers, > suman > > > > -- > View this message in context: > http://pmapper-users-p-mapper-users-mailing-list.993774.n3.nabble.com/pmapper-users-join-to-postgresql-table-problem-tp3822408p4025160.html > Sent from the pmapper-users -- p.mapper users mailing list mailing list > archive at Nabble.com. > > -- > Try New Relic Now & We'll Send You this Cool Shirt > New Relic is the only SaaS-based application performance monitoring service > that delivers powerful full stack analytics. Optimize and monitor your > browser, app, & servers with just a few lines of code. Try New Relic > and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr > ___ > pmapper-users mailing list > pmapper-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/pmapper-users > > -- Try New Relic Now & We'll Send You this Cool Shirt New Relic is the only SaaS-based application performance monitoring service that delivers powerful full stack analytics. Optimize and monitor your browser, app, & servers with just a few lines of code. Try New Relic and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr ___ pmapper-users mailing list pmapper-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/pmapper-users
Re: [pmapper-users] join to postgresql table problem
hello Valerio, sorry for late reply, if you r still searching the answer and anybody searching for this answer. then here is solution. As you asked for to display joined records from external table in query identify div window, you need to query from DATA tag in map file. like this. LAYER NAME "Building Footprint" TYPE polygon CONNECTIONTYPE postgis CONNECTION "dbname=databasename host=localhost port=5432 user=postgres password=xxx" DATA "geom FROM (SELECT a.geom as geom, a.gid as gid, b.tole as tole_b1, b.houseid as houseid, b.photono as photono FROM tbl_building a, tbl_socio_economic b WHERE a.join_id = b.join_id) AS building_socio USING UNIQUE gid" TRANSPARENCY 30 PROCESSING "CLOSE_CONNECTION=DEFER" TEMPLATE void METADATA "DESCRIPTION" "Building Footprint" ## ADAPTED TO MULTILINGUAL SHAPEFILE "RESULT_FIELDS" "tole_b1,houseid,photono,area,funct_use" "RESULT_HEADERS" "tole_b1,houseid,photono,AREA,funct_use" "ows_title" "Ward" END # Metadata CLASS Name 'All' COLOR 223 219 212 OUTLINECOLOR 223 219 212 END # Class END # Layer And i am using pmapper 4.2.0... and you can query as more field as you can specifying in DATA query. cheers, suman -- View this message in context: http://pmapper-users-p-mapper-users-mailing-list.993774.n3.nabble.com/pmapper-users-join-to-postgresql-table-problem-tp3822408p4025160.html Sent from the pmapper-users -- p.mapper users mailing list mailing list archive at Nabble.com. -- Try New Relic Now & We'll Send You this Cool Shirt New Relic is the only SaaS-based application performance monitoring service that delivers powerful full stack analytics. Optimize and monitor your browser, app, & servers with just a few lines of code. Try New Relic and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr ___ pmapper-users mailing list pmapper-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/pmapper-users
Re: [pmapper-users] join to postgresql table problem
Hi Chris,
thanks for your reply but my problem is to display joined records from
external table in query identify div window.
I think this is a specific pmapper way to do that but when I uncomment the
RESULT_JOIN tag in medatada I get
the not-stopping wheel
"RESULT_JOIN" "pgsql://
postgres:mypass@127.0.0.1:5432/cbam_db||cens@ident@0@ident,note||FID||0"
Tried with numeric, string fields, other layers but without success.
This is the pm debug log:
***
[15-Mar-2012 17:02:20] P.MAPPER debug info
Validation of search.xml file FAILED:
Warning: DOMDocument::schemaValidate(): Element 'searchitem': Missing child
element(s). Expected is ( layer ). in /var/www/cbam/incphp/query/search.php
on line 71
***
I guess it cannot find postgres db or the "cens" table but this string
works fine in php files:
$connstr = "dbname=cbam_db user=postgres password=mypasshost=localhost
port=5432";
$dbh = pg_connect($connstr);
$sql2 = "SELECT * FROM cens";
$stat2 = pg_exec($dbh, $sql2);
$data2 = pg_fetch_array($stat2, $i2);
**
So, it would be very important for me if someone could tell me where in
source code I can get the layers and the values retrieved from identify
tool in order to manually insert the connection code to postgresql db.
I looked around the code but my programming skills are poor. Tried also to
insert test code in query.php and squery.php in order to get those
informations but every time I change something in those files (even a
simple "echo") the identify tool stops to work.
thank you very much.
Paolo
2012/3/15 Chris forum
> Hello Paolo,
>
> Here under a working example on how to connect to postgresql/postgis table
> (used with Cartoweb) :
>
> LAYER
> NAME "tree"
> TYPE POINT
> STATUS ON
> METADATA
> "id_attribute_string" "id_object"
> "query_returned_attributes" "id_object"
> END
> SYMBOLSCALE 1000
> CONNECTIONTYPE postgis
> CONNECTION "user= password= dbname= host=localhost port=5432"
> DATA "the_geom from tree using unique id_object using srid=21781"
> TEMPLATE "blabla"
> LABELITEM "id_object"
> MAXSCALEDENOM 4990
> SYMBOLSCALEDENOM 1000
> POSTLABELCACHE ON
> CLASS
> STYLE
> SYMBOL "circle"
> COLOR 255 0 125
> MINSIZE 1
> SIZE 6
> MAXSIZE 10
> END
> END
> END
>
>
> You could also try to use QGIS and check if you are able to connect to
> your postgresql table.
>
> Hope that helps!
>
> BaK
>
>
>
> On Tue, Mar 13, 2012 at 8:53 AM, VT wrote:
>
>> Hi,
>> I'm trying to join a postgres table to shapefile layer (one-to-many) but I
>> get no response from identify tool.
>> Connection to postgres works correctly in a separate php file:
>>
>> $connstr = "dbname=cbam_db user=postgres password=mypass host=localhost
>> port=5432";
>>
>>$dbh = pg_connect($connstr);
>>if ($dbh)
>>{
>>//do nothing... go on...
>>echo "...ok...";
>>}
>>else
>>{
>>echo "troubles";
>>}
>>$sql2 = "SELECT * FROM cbam_table";
>>$stat2 = pg_exec($dbh, $sql2);
>>$data2 = pg_fetch_array($stat2, $i2);
>> for ($i2 = 0; $i2 < $rows2; $i2++)
>>echo $data2["categoria"];
>> }
>>
>>
>>
>> **
>>
>> this is my mapfile piece:
>>
>>
>> LAYER
>> NAME "censnew"
>> TYPE line
>> DATA "censnew"
>> CLASSITEM "Categ"
>> TEMPLATE void
>> METADATA
>>"DESCRIPTION" "Reticolo di competenza"
>>"RESULT_FIELDS" "NOME,COMUNE,CATEG,LUNGH"
>>"RESULT_HEADERS" "Nome,Comune,Categoria,Lunghezza(m)"
>>"ows_title" "Reticolo di competenza"
>>"RESULT_JOIN" "pgsql://
>>
>> postgres:mypass@127.0.0.1:5432/cbam_db||cbam_table@categoria@1@categoria||CATEG||1
>> "
>> END
>> ...
>> ...
>>
>> postgresql table "cbam_table" has one text column named "categoria".
>> I tried with localhost instead of 127.0.0.1 but query window doesn't
>> display. All works fine if I comment "RESULT_JOIN" row.
>>
>> Where am i wrong?
>>
>> thank you very much
>>
>> Paolo
>>
>> --
>> Keep Your Developer Skills Current with LearnDevNow!
>> The most comprehensive online learning library for Microsoft developers
>> is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
>> Metro Style Apps, more. Free future releases when you subscribe now!
>> http://p.sf.net/sfu/learndevnow-d2d
>> ___
>> pmapper-users mailing list
>> pmapper-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/pmapper-users
>>
>
>
WebRep
Overall rating
--
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
___
pmapper-users mailing list
pm
Re: [pmapper-users] join to postgresql table problem
Hello Paolo,
Here under a working example on how to connect to postgresql/postgis table
(used with Cartoweb) :
LAYER
NAME "tree"
TYPE POINT
STATUS ON
METADATA
"id_attribute_string" "id_object"
"query_returned_attributes" "id_object"
END
SYMBOLSCALE 1000
CONNECTIONTYPE postgis
CONNECTION "user= password= dbname= host=localhost port=5432"
DATA "the_geom from tree using unique id_object using srid=21781"
TEMPLATE "blabla"
LABELITEM "id_object"
MAXSCALEDENOM 4990
SYMBOLSCALEDENOM 1000
POSTLABELCACHE ON
CLASS
STYLE
SYMBOL "circle"
COLOR 255 0 125
MINSIZE 1
SIZE 6
MAXSIZE 10
END
END
END
You could also try to use QGIS and check if you are able to connect to your
postgresql table.
Hope that helps!
BaK
On Tue, Mar 13, 2012 at 8:53 AM, VT wrote:
> Hi,
> I'm trying to join a postgres table to shapefile layer (one-to-many) but I
> get no response from identify tool.
> Connection to postgres works correctly in a separate php file:
>
> $connstr = "dbname=cbam_db user=postgres password=mypass host=localhost
> port=5432";
>
>$dbh = pg_connect($connstr);
>if ($dbh)
>{
>//do nothing... go on...
>echo "...ok...";
>}
>else
>{
>echo "troubles";
>}
>$sql2 = "SELECT * FROM cbam_table";
>$stat2 = pg_exec($dbh, $sql2);
>$data2 = pg_fetch_array($stat2, $i2);
> for ($i2 = 0; $i2 < $rows2; $i2++)
>echo $data2["categoria"];
> }
>
>
>
> **
>
> this is my mapfile piece:
>
>
> LAYER
> NAME "censnew"
> TYPE line
> DATA "censnew"
> CLASSITEM "Categ"
> TEMPLATE void
> METADATA
>"DESCRIPTION" "Reticolo di competenza"
>"RESULT_FIELDS" "NOME,COMUNE,CATEG,LUNGH"
>"RESULT_HEADERS" "Nome,Comune,Categoria,Lunghezza(m)"
>"ows_title" "Reticolo di competenza"
>"RESULT_JOIN" "pgsql://
>
> postgres:mypass@127.0.0.1:5432/cbam_db||cbam_table@categoria@1@categoria||CATEG||1
> "
> END
> ...
> ...
>
> postgresql table "cbam_table" has one text column named "categoria".
> I tried with localhost instead of 127.0.0.1 but query window doesn't
> display. All works fine if I comment "RESULT_JOIN" row.
>
> Where am i wrong?
>
> thank you very much
>
> Paolo
>
> --
> Keep Your Developer Skills Current with LearnDevNow!
> The most comprehensive online learning library for Microsoft developers
> is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
> Metro Style Apps, more. Free future releases when you subscribe now!
> http://p.sf.net/sfu/learndevnow-d2d
> ___
> pmapper-users mailing list
> pmapper-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/pmapper-users
>
--
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
___
pmapper-users mailing list
pmapper-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/pmapper-users
[pmapper-users] join to postgresql table problem
Hi,
I'm trying to join a postgres table to shapefile layer (one-to-many) but I
get no response from identify tool.
Connection to postgres works correctly in a separate php file:
$connstr = "dbname=cbam_db user=postgres password=mypass host=localhost
port=5432";
$dbh = pg_connect($connstr);
if ($dbh)
{
//do nothing... go on...
echo "...ok...";
}
else
{
echo "troubles";
}
$sql2 = "SELECT * FROM cbam_table";
$stat2 = pg_exec($dbh, $sql2);
$data2 = pg_fetch_array($stat2, $i2);
for ($i2 = 0; $i2 < $rows2; $i2++)
echo $data2["categoria"];
}
**
this is my mapfile piece:
LAYER
NAME "censnew"
TYPE line
DATA "censnew"
CLASSITEM "Categ"
TEMPLATE void
METADATA
"DESCRIPTION" "Reticolo di competenza"
"RESULT_FIELDS" "NOME,COMUNE,CATEG,LUNGH"
"RESULT_HEADERS" "Nome,Comune,Categoria,Lunghezza(m)"
"ows_title" "Reticolo di competenza"
"RESULT_JOIN" "pgsql://
postgres:mypass@127.0.0.1:5432/cbam_db||cbam_table@categoria@1@categoria||CATEG||1
"
END
...
...
postgresql table "cbam_table" has one text column named "categoria".
I tried with localhost instead of 127.0.0.1 but query window doesn't
display. All works fine if I comment "RESULT_JOIN" row.
Where am i wrong?
thank you very much
Paolo
--
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
___
pmapper-users mailing list
pmapper-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/pmapper-users
