[Mapserver-users] Coloring shapefile layer according to DBquery

Fri Oct 10 17:09:53 PDT 2003

--0-279380618-1065830993=:39270
Content-Type: text/plain; charset=us-ascii

     In my experience ("View"), having the Spatial and Attribute information tied together in a single table ( or View) is the fastest and most efficient way to create Themematic Maps. Your MS map files only need a simple Select and Filter to color each layer. I believe this to be a very stable method. Although not as elegant or ego fulfilling as writing code to do all this on the fly. However, the work to preprocess ("join") the information can be as demanding.
     So, in my opinion, since you don't want to deal with PostGIS. I would save a base copy of the original spatial (Shp Files) information. Then follow Steve's instructions.

     Working with PostGIS indexing the attribute information in addition to the spatial data, I plan to query is just as or a little more faster than reading my original .TAB files. The benefit I get from using PostGIS is an easier ("cheaper") way to update the attribute data. A simple update query. Where as Dealing with Shp or TAB would require little coding.

Matthew Haines <mhaines94108 at hotmail.com> wrote:

So which is faster, reading the shapefiles from the DBF file or (assuming I 
move to PostgreSQL) reading them from a PostgreSQL database?

Reading between the lines I'm guessing that (1) the .DBF files are an 
indexed database file, (2) mapserver has an XBASE database engine built in, 
and (3) XBASE is the fastest database for this kind of application, and 
therefore (4) switching to MySQL or PostgreSQL would be a form of 
downgrading.

Precompiling everything isn't quite as sexy as doing doing it on the fly, 
but speed is very important, and not having to add PostegreSQL to my server 
and migrate everything from MySQL is also good.

Matthew

----Original Message Follows----
From: "Steve Lime" 
To: ,
Subject: Re: [Mapserver-users] Coloring shapefile layer according to DBquery
Date: Fri, 10 Oct 2003 15:11:54 -0500

An increasingly popular topic these days.

There is no way with CGI MapServer to link the two for map creation.
Your best bet
would be to create a custom shapefile that combines the base shapefile
and the MySQL
database. You can use MapScript and the DBD drivers for MySQL and XBase
to do this
pretty easily. The psuedo code is something like:

- copy the base shapefile to a new file
- open the new shapefile DBF file
- add the new columns
- open the connection to MySQL
- for each shape select the corresponding MySQL row
- copy the content from MySQL to the DBF file
- close everything

Really shouldn't be more that 20 lines.

If done properly you could run this script every so often to sync the
2
datasets using cron. The advantage of this approach is that it gives
you a dataset
optimized for map making. The real time joins are certainly doable but
would absolutely
kill performance- that's why they're not supported.

If your MySQL data is not changing every few seconds then this approach
should work
quite well. Postgres/PostGIS is of no help unless you want to move
everything into it and
not use MySQL.

Steve

Stephen Lime
Data & Applications Manager

Minnesota DNR
500 Lafayette Road
St. Paul, MN 55155
651-297-2937

>>> "Matthew Haines" 10/10/2003 2:20:18 PM
>>>

I have a shapefile for the tax lots in Manhattan (New York City) and I
have
a lot of information about the individual tax lots in a MySQL database.
I
would like to generate maps where I color the tax lots according to the
data
in the MySQL database.

Just as an example, I have a table that tells me how many residential
units
have been built on a particular tax lot. I might like to color the map
by
population density so that single family dwellings are light red, 2-4
family
buildings are medium red, and 5+ family apartment buildings are dark
red.

Is there any way to do this without loading the shapefile polygons into
the
database? I've had several thoughts, but they seem impractical ...

=> I could use Perl MapScript to write a program that would scan the
entire
database table and assign tax lots to different layers ... but there
are
100,000 tax lots in Manhattan and that might take a long time.

=> I could try to get put the data into the shapefile ... but how? I
don't
have any commercial mapping software for working with shapefiles. Is
there
a utility to do this? Also that makes my data hard to update.

=> Is there a MAP file command to load data from a database and somehow
join
it to the shapefile on the fly so I can define layers based on it?

I'm hesitant to mess with MySQL, if only for fear of breaking my
existing
applications. The documentation indicates that the MySQL connector is

buggy.

What's the best way to go about this? Do I have to install
PostgreSQL?

Pointers much appreciated,

Matthew

Matthew

_________________________________________________________________
Share your photos without swamping your Inbox. Get Hotmail Extra
Storage
today! http://join.msn.com/?PAGE=features/es

_______________________________________________
Mapserver-users mailing list
Mapserver-users at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users

_________________________________________________________________
Add MSN 8 Internet Software to your existing Internet access and enjoy 
patented spam protection and more. Sign up now! 
http://join.msn.com/?page=dept/byoa

_______________________________________________
Mapserver-users mailing list
Mapserver-users at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users

---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
--0-279380618-1065830993=:39270
Content-Type: text/html; charset=us-ascii

<DIV>     In my experience ("View"), having the Spatial and Attribute information tied together in a single table ( or View) is the fastest and most efficient way to create Themematic Maps. Your MS map files only need a simple Select and Filter to color each layer. I believe this to be a very <U>stable</U> method. Although not as elegant or ego fulfilling as writing code to do all this on the fly. However, the work to preprocess ("join") the information can be as demanding.</DIV>
<DIV>     So, in <U>my opinion</U>, since you don't want to deal with PostGIS. I would save a base copy of the original spatial (Shp Files) information. Then follow Steve's instructions.</DIV>
<DIV> </DIV>
<DIV>     Working with PostGIS indexing the attribute information in addition to the spatial data, I plan to query is just as or a little more faster than reading my original .TAB files. The benefit I get from using PostGIS is an easier ("cheaper") way to update the attribute data. A simple update query. Where as Dealing with Shp or TAB would require little coding.</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><B><I>Matthew Haines <mhaines94108 at hotmail.com></I></B> wrote:</DIV>
<BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid"><BR>So which is faster, reading the shapefiles from the DBF file or (assuming I <BR>move to PostgreSQL) reading them from a PostgreSQL database?<BR><BR>Reading between the lines I'm guessing that (1) the .DBF files are an <BR>indexed database file, (2) mapserver has an XBASE database engine built in, <BR>and (3) XBASE is the fastest database for this kind of application, and <BR>therefore (4) switching to MySQL or PostgreSQL would be a form of <BR>downgrading.<BR><BR>Precompiling everything isn't quite as sexy as doing doing it on the fly, <BR>but speed is very important, and not having to add PostegreSQL to my server <BR>and migrate everything from MySQL is also good.<BR><BR>Matthew<BR><BR>----Original Message Follows----<BR>From: "Steve Lime" <STEVE.LIME at DNR.STATE.MN.US><BR>To: <MHAINES94108 at HOTMAIL.COM>,<MAPSERVER-USERS at LISTS.GIS.UMN.EDU><BR>Subject: Re: [Mapserver-users] Coloring
 shapefile layer according to DBquery<BR>Date: Fri, 10 Oct 2003 15:11:54 -0500<BR><BR>An increasingly popular topic these days.<BR><BR>There is no way with CGI MapServer to link the two for map creation.<BR>Your best bet<BR>would be to create a custom shapefile that combines the base shapefile<BR>and the MySQL<BR>database. You can use MapScript and the DBD drivers for MySQL and XBase<BR>to do this<BR>pretty easily. The psuedo code is something like:<BR><BR>- copy the base shapefile to a new file<BR>- open the new shapefile DBF file<BR>- add the new columns<BR>- open the connection to MySQL<BR>- for each shape select the corresponding MySQL row<BR>- copy the content from MySQL to the DBF file<BR>- close everything<BR><BR>Really shouldn't be more that 20 lines.<BR><BR>If done properly you could run this script every so often to sync the<BR>2<BR>datasets using cron. The advantage of this approach is that it gives<BR>you a dataset<BR>optimized for map making. The real time joins are
 certainly doable but<BR>would absolutely<BR>kill performance- that's why they're not supported.<BR><BR>If your MySQL data is not changing every few seconds then this approach<BR>should work<BR>quite well. Postgres/PostGIS is of no help unless you want to move<BR>everything into it and<BR>not use MySQL.<BR><BR>Steve<BR><BR>Stephen Lime<BR>Data & Applications Manager<BR><BR>Minnesota DNR<BR>500 Lafayette Road<BR>St. Paul, MN 55155<BR>651-297-2937<BR><BR>>>> "Matthew Haines" <MHAINES94108 at HOTMAIL.COM>10/10/2003 2:20:18 PM<BR>>>><BR><BR>I have a shapefile for the tax lots in Manhattan (New York City) and I<BR>have<BR>a lot of information about the individual tax lots in a MySQL database.<BR>I<BR>would like to generate maps where I color the tax lots according to the<BR>data<BR>in the MySQL database.<BR><BR>Just as an example, I have a table that tells me how many residential<BR>units<BR>have been built on a particular tax lot. I might like to color the
 map<BR>by<BR>population density so that single family dwellings are light red, 2-4<BR>family<BR>buildings are medium red, and 5+ family apartment buildings are dark<BR>red.<BR><BR>Is there any way to do this without loading the shapefile polygons into<BR>the<BR>database? I've had several thoughts, but they seem impractical ...<BR><BR>=> I could use Perl MapScript to write a program that would scan the<BR>entire<BR>database table and assign tax lots to different layers ... but there<BR>are<BR>100,000 tax lots in Manhattan and that might take a long time.<BR><BR>=> I could try to get put the data into the shapefile ... but how? I<BR>don't<BR>have any commercial mapping software for working with shapefiles. Is<BR>there<BR>a utility to do this? Also that makes my data hard to update.<BR><BR>=> Is there a MAP file command to load data from a database and somehow<BR>join<BR>it to the shapefile on the fly so I can define layers based on it?<BR><BR>I'm hesitant to mess with MySQL,
 if only for fear of breaking my<BR>existing<BR>applications. The documentation indicates that the MySQL connector is<BR><BR>buggy.<BR><BR>What's the best way to go about this? Do I have to install<BR>PostgreSQL?<BR><BR>Pointers much appreciated,<BR><BR>Matthew<BR><BR>Matthew<BR><BR>_________________________________________________________________<BR>Share your photos without swamping your Inbox. Get Hotmail Extra<BR>Storage<BR>today! http://join.msn.com/?PAGE=features/es<BR><BR>_______________________________________________<BR>Mapserver-users mailing list<BR>Mapserver-users at lists.gis.umn.edu<BR>http://lists.gis.umn.edu/mailman/listinfo/mapserver-users<BR><BR>_________________________________________________________________<BR>Add MSN 8 Internet Software to your existing Internet access and enjoy <BR>patented spam protection and more. Sign up now! <BR>http://join.msn.com/?page=dept/byoa<BR><BR>_______________________________________________<BR>Mapserver-users mailing
 list<BR>Mapserver-users at lists.gis.umn.edu<BR>http://lists.gis.umn.edu/mailman/listinfo/mapserver-users</BLOCKQUOTE><p><hr SIZE=1>
Do you Yahoo!?<br>
<a href="http://shopping.yahoo.com/?__yltc=s%3A150000443%2Cd%3A22708228%2Cslk%3Atext%2Csec%3Amail">The New Yahoo! Shopping</a> - with improved product search
--0-279380618-1065830993=:39270--