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, 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
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 bak.fo...@gmail.com
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 notis...@gmail.com 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 troublesbr;
}
$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||1http://postgres:mypass@127.0.0.1:5432/cbam_db%7C%7Ccbam_table@categoria@1@categoria%7C%7CCATEG%7C%7C1
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
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 troublesbr;
}
$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
