[mapserver-users] Re: Join from SHP file to PostgreSQL table

[Brent Fraser]

Flavio,

   I'm currently experimenting with joining a shapefile to a MS Access 
file using OGR and ODBC (and stock mapserver v 5.2.1 from MS4W).   Works ok:

     CONNECTIONTYPE OGR
     CONNECTION     'G:/GeoData/Parcels/Alberta/t_505033.shp'
     DATA "SELECT * FROM t_505033 LEFT JOIN 'ODBC:Parcels_Rural'.basic 
ON t_505033.PID=basic.PID"

I'm able to use a column in the ODBC datasource as the CLASSITEM for 
rendering.  You should be able to do the same with an ODBC datasource 
pointing to a PostGIS database...

Best Regards,
Brent Fraser


On 11/29/2010 12:49 PM, Flavio Carmo wrote:
> Hi,
>
> Sorry, but i cant upload the shapes to postgres. The shapefiles are 
> modified using arcview and the data in the postgres is modified using 
> a custom desktop software that my client uses for about 5 years. The 
> software open the shapefiles, then using a ODBC connection with 
> postgres, make the join in memory and shows the information for each 
> geometry.
>
> I cant undo this situation, so, my client propose to me to uses a 
> opensource solution to also make this join in a WMS service. He knows 
> Geoserver and Mapserver. Geoserver cant do this too, so i've hoped 
> that mapserver could be a solution.
>
> Regards,
>
> Flávio Carmo
>
>
> On Mon, Nov 29, 2010 at 16:35, Stephen Woodbridge 
> <woodbri at swoodbridge.com <mailto:woodbri at swoodbridge.com>> wrote:
>
>     On 11/29/2010 2:21 PM, flaviocarmo wrote:
>
>
>         Hi,
>
>         Thanks all for your responses. Im a novice in Mapserver so,
>         after reading,
>         now i know that:
>
>         - I cant use a JOIN to shown only geometrys with the CODE
>         field stored on
>         Postgres (or another Database) because joins cannot modify the
>         look of a
>         map;
>
>         - I need to use a template always to show the queries results;
>
>         - Case-sensitive fields can be a problem in a query with
>         mapserver;
>
>         - The "PG:" string for was my mistake. I missed in the mail,
>         but was in my
>         MAP file. Sorry.
>
>         Now what? There isnt a way to solve my issue? My client needs
>         a opensource
>         solution to deliver his data. His current solution (ESRI
>         ArcGIS Server) is
>         too expensive to buy another license, so he asked for a
>         solution with the
>         same quality, but opensource.
>
>         There is a way to workaround this?
>
>
>     Favlio,
>
>     I would put all the data in a postgres/postgis database and
>     display it from there. There is a simple shapefile loader:
>      shp2pgsql. In this setup you can easily join tables, create
>     inherited tables if you are working with data like streets.shp for
>     multiple countries so the parent table can be used to seamlessly
>     display the multiple streets tables as a single layer in mapserver.
>
>     He may have to rethink his work flow a little because the tools
>     are different, but this would be the case regardless if he is
>     changing the software that he is using.
>
>     -Steve W
>
>     _______________________________________________
>     mapserver-users mailing list
>     mapserver-users at lists.osgeo.org
>     <mailto:mapserver-users at lists.osgeo.org>
>     http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20101129/1e272417/attachment.html