[Geomoose-users] [Geomoose-developers] MySQL SOLVED........

[Jay Kapalczynski]

If any of you out there are interested, with the help of Nelson (thanks again) I was successful in getting MySQL installed on my computer, a user created and then linked this database up to GeoMOOSE.

Reason:
I am using Access to capture XY locations from a GPS unit
In Access I am updating a Table that is in MySQL
I then use GeoMOOSE to plot these XY locations in the map...if you were wireless it would be live every refresh


Could lead into vehicle tracking



Download and Install three files from MySQL

Files:

 *   Install Community Server first
    *   http://dev.mysql.com/downloads/mysql/5.0.html#downloads
 *   Then install GUI Tools
    *   http://dev.mysql.com/downloads/gui-tools/5.0.html
 *   Download and install the ODBC Driver
    *   http://dev.mysql.com/downloads/connector/odbc/5.1.html
 *   Create a new User in the Database and give privleges
 *   Set up an ODBC to the database




Connect up to GeoMOOSE and retrieve data

Three things have to be set to use this data in GeoMOOSE:

 *   Create .ovf file
 *   Create a new Map file
 *   Modify the MapleGrove_demo.xml file

Create the .ovf file.

Create a new txt file and rename it to "Coordinatedata.ovf"
Please the file in " C:\ms4w\apps\GeoMOOSE\Maple_Grove_GeoMoose\Databases" or where ever you are storing the map file for the database
Add this code:

<OGRVRTDataSource>
    <OGRVRTLayer name="Coordinatedata">
        <SrcDataSource>MYSQL:coordinates_database,user=User name in Database,password=Passowrd to database,host=127.0.0.1,port=3306,tables=coordinates</SrcDataSource>
        <SrcSQL>SELECT X,Y FROM coordinates</SrcSQL>
        <GeometryType>wkbPoint</GeometryType>
                                <GeometryField encoding="PointFromColumns" x="X" y="Y"/>
                </OGRVRTLayer>
</OGRVRTDataSource>

*         Coordinates_database is the database name in MySQL
*         User and password set for a specific user
*         Host - local machine so 127.0.0.1
*         Port was assigned on install
*         Next is the SQL SELECT Statement
*         Geometry sets to a point
*         Geometry Field assigns XY to fields




Create the Map File...create a new map file and add this code.  Save it somewhere...save the above ovf file in the same location

MAP
                NAME 'Database'
                SIZE 800 650
                STATUS ON
                EXTENT 189783.560000 4816309.330000 761653.524114 5472346.500000
                UNITS METERS
                FONTSET '../fonts/fontset.list'
                SYMBOLSET '../symbols/symbol.sym'
                TRANSPARENT TRUE
                IMAGETYPE PNG

                QUERYMAP
                                STATUS ON
                                STYLE SELECTED
                END

                WEB
                                  INCLUDE "/ms4w/apps/GeoMOOSE/Maple_Grove_GeoMoose/web_config.map"
                END

                LEGEND
                                                STATUS ON
                END

LAYER # Points from the MySQL Database
    NAME Database                                                                 # MATCHES THE MAPBOOK IN THE demo.xml FILE
    STATUS DEFAULT
    TYPE POINT
    CONNECTIONTYPE OGR
    CONNECTION './Coordinatedata.ovf'                                 # THIS IS THE NAME OF THE ovf FILE
    DATA 'Coordinatedata'                                       # THIS IS THE NAME OF THE FUNCTION INSIDE THE ovf FILE
    CLASS
       #NAME "ID"
           STYLE
               SYMBOL "circle"
               SIZE 40
               COLOR 0 100 100
           END
    END
END
END ## end Map


MODIFY THE MapleGrove_demo.xml file...call the map file created above to draw the points

   <map title="Database" default="true" reference="false">
                <file>/ms4w/apps/GeoMOOSE/Maple_Grove_GeoMoose/Databases/Coordinate_Points_8bit.map</file>
  </map>






From: Nelson Soto [mailto:NSoto at plotplans.com]
Sent: Friday, November 07, 2008 10:08 AM
To: Jay Kapalczynski; geomoose-developers at lists.sourceforge.net; geomoose-users at lists.sourceforge.net
Subject: RE: [Geomoose-developers] [Geomoose-users] MySQL

The ogrvrt code is supposed to go in the ovf file you are told to make here: http://mapserver.gis.umn.edu/docs/howto/ogrmysql/?searchterm=ovf

It technically doesn't matter where you save from what I gather; Just point to wherever you put it in your map file. So, probably in the same folder as your map file if you want to try to be consistent. It's really up to you..

________________________________
From: Jay Kapalczynski [mailto:jkapalczynski at ci.maple-grove.mn.us]
Sent: Friday, November 07, 2008 10:48 AM
To: 'geomoose-developers at lists.sourceforge.net'; 'geomoose-users at lists.sourceforge.net'
Subject: Re: [Geomoose-developers] [Geomoose-users] MySQL

If I am doing something like this where does the  OGRVRTDataSource  code have to be?





<OGRVRTDataSource>

    <OGRVRTLayer name="aqidata">

        <SrcDataSource>MYSQL:aqiTest,user=uuuuu,password=ppppp,host=192.170.1.100,port=3306,tables=testdata</SrcDataSource>

        <SrcSQL>SELECT areaID, x, y, sampleValue FROM testdata</SrcSQL>

        <GeometryType>wkbPoint</GeometryType>

        <GeometryField encoding="PointFromColumns" x="x" y="y"/>

    </OGRVRTLayer>

</OGRVRTDataSource>




LAYER
    NAME "MyAqi"
    STATUS DEFAULT
    TYPE POINT
    CONNECTIONTYPE OGR
    CONNECTION "aqidata.ovf"
    DATA "aqidata"
    CLASS
       NAME "MyClass"
           STYLE
               SYMBOL 'circle'
               SIZE 15
               COLOR 0 255 0
           END
    END
END
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/geomoose-users/attachments/20081107/43d9d930/attachment.html