[gdal-dev] drawing line using OGR and mysql in mapserver

Murat Beyhan beyhan at deprem.gov.tr
Tue Feb 28 02:19:37 EST 2012


Chaitanya,


Map file which is try to draw line as follows 
are there any problem on the code

 LAYER
 NAME sfault1
 CONNECTIONTYPE OGR
 CONNECTION '<OGRVRTDataSource>
 <OGRVRTLayer name="lineview">
<SrcDataSource>MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306,tables=lineview</SrcDataSource>
 <SrcLayer>lineview</SrcLayer>
 <GeometryType>wkbLineString</GeometryType>
 <GeometryField encoding="WKT" field="geom"/>
 <LayerSRS>WGS84</LayerSRS>
 <FID>eq_id</FID>
 </OGRVRTLayer>
 </OGRVRTDataSource>'
  DATA sfault1
  STATUS on
  TYPE line
  CLASS
  NAME "Source Fault"
  SYMBOL "cline"
   SIZE 3
  COLOR  0 0 255
  END
  END 

ogrinfo
MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306
sfault -summary
INFO: Open of
`MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306'
      using driver `MySQL' successful.

Layer name: sfault
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
eq_id: String (14.0)
lat1: Real (7.4)
lon1: Real (7.4)
lat2: Real (7.4)
lon2: Real (7.4)

**************linewiew created by following sql statement:*************


 create view lineview as select eq_id, "LINESTRING(" + lon1 + " " + lat1
 + "," + lon2 + " " + lat2 + ")" as geom from sfault 

then

[murat at localhost ~]$ ogrinfo
MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306
lineview -summary
INFO: Open of
`MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306'
      using driver `MySQL' successful.

Layer name: lineview
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
eq_id: String (14.0)
geom: Real (0.0)


as you see there is no problem to connect mysql and read information
created mysql geometries from shape file also works here is an example

[murat at localhost ~]$ ogrinfo
MYSQL:testogr,user=root,password=mysqlpasswd,host=localhost,port=3306
iller -summary
INFO: Open of
`MYSQL:testogr,user=root,password=mysqlpasswd,host=localhost,port=3306'                            
      using driver `MySQL'
successful.                                                                         

Layer name: iller
Geometry: Polygon
Feature Count: 83
Extent: (26.026489, 35.820221) - (44.869179, 42.094917)
Layer SRS WKT:
GEOGCS["GCS_WGS_1984",
    DATUM["WGS_1984",
        SPHEROID["WGS_1984",6378137,298.257223563]],
    PRIMEM["Greenwich",0],
    UNIT["Degree",0.0174532925199433]]
FID Column = OGR_FID
Geometry Column = SHAPE2
area: Real (13.6)
perimeter: Real (13.6)
illerdd_: Real (0.0)
illerdd_id: Real (0.0)
il_kod: Real (2.0)
iladi: String (20.0)
[murat at localhost ~]$



Now I would like to learn what is wrong with my procedures... please
help



Murat


On Tue, 2012-02-28 at 09:03 +0530, Chaitanya kumar CH wrote:
> Murat,
> 
> 
> Since it is MySQL it doesn't matter much how you store it. If you are
> going to edit the end points later, storing them as points is
> preferable, otherwise store them as wkt linestring geometries.
> 
> 
> For your mapfile, first check if the extents are correct. Then check
> if the mapfile works with a shapefile created from the geometries in
> MySQL.
> 
> On Tuesday, February 28, 2012, Murat BEYHAN wrote:
>         Chaitanya,
>         
>         thanks for your repply,
>         
>         Actually I use ogr and mysql to draw point for example
>         epicenter of
>         earthquakes. But this is the first time to draw line which is
>         stored in
>         Mysql as a table. Actually there is no problem on drawing
>         shape file of the
>         line. But I would like to draw it using MySql and ogr
>         functionality. So I
>         have only start and end point of line and I store it on MySql
>         database then
>         I try to draw it considering id of the line by sql statement.
>         Just I would lie to learn how to code it on Mapserver map file
>         and how
>         should I store line on database as a start and stop point of
>         the line or as
>         a geometry . Please help...
>         thanks
>         
>         
>         
>         
>         On Mon, 27 Feb 2012 22:09:52 +0530, Chaitanya kumar CH wrote
>         > Murat,
>         >
>         > First check if your database is accessible using ogrinfo[1].
>         > Then create a vrt file with the content you specified in the
>         CONNECTION
>         > field of the mapfile. Check if ogrinfo gives expected
>         results with this
>         > file.
>         >
>         > You probably have gdal executables if you are running
>         mapserver.
>         >
>         > [1]: http://www.gdal.org/ogrinfo.html
>         >
>         > On Mon, Feb 27, 2012 at 12:48 PM, Murat Beyhan
>         > <beyhan at deprem.gov.tr> wrote:
>         >
>         > > Dear all,
>         > > I have shared this mail on Mapserver list but could not
>         solve my
>         > > problem.
>         > > Still could not achieve to draw line using start and stop
>         point line
>         > > data stored in MySql database.
>         > >
>         > > I have tried Mr. Robert's solution but I have faced an
>         error message.
>         > >
>         > >
>         > > in map file
>         > > I changed Geometrytype from wkbline to wkblinestring.
>         > >
>         > > LAYER
>         > > NAME sfault1
>         > > CONNECTIONTYPE OGR
>         > > CONNECTION '<OGRVRTDataSource>
>         > > <OGRVRTLayer name="lineview">
>         > >
>         > >
>         <SrcDataSource>MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,po
>         rt=3306,tables=lineview</SrcDataSource>
>         > > <SrcLayer>lineview</SrcLayer>
>         > > <GeometryType>wkbLineString</GeometryType>
>         > > <GeometryField encoding="WKT" field="geom"/>
>         > > <LayerSRS>WGS84</LayerSRS>
>         > > <FID>eq_id</FID>
>         > > </OGRVRTLayer>
>         > > </OGRVRTDataSource>'
>         > >  DATA sfault1
>         > >  STATUS on
>         > >  TYPE line
>         > >  CLASS
>         > >  NAME "Source Fault"
>         > >  SYMBOL "cline"
>         > >   SIZE 3
>         > >  COLOR  0 0 255
>         > >  END
>         > > END
>         > >
>         > > in database
>         > > I have two table
>         > > one is sfault which store
>         > >
>         > > id lat1 lon1 lat2 lon2
>         > >
>         > > and another is lineview which is a view
>         > > of this table created by following
>         > >
>         > > create view lineview as select eq_id, "LINESTRING(" + lon1
>         + " " + lat1
>         > > + "," + lon2 + " " + lat2 + ")" as geom from sfault
>         > >
>         > >
>         > > there is an another issue,
>         > >
>         > > I have to draw line by considering id
>         > > so in the map When I select one id, line should be belong
>         to this id's
>         > > information
>         > > so I have to make a sql statement as follow instead of
>         draw all line
>         > > stored in database I have to draw just one line
>         considering id
>         > >
>         > > something like this:
>         > >
>         > > select eq_id, geom from lineview where id like '%id%'
>         > >
>         > >
>         > > please help about this subject....
>         > >
>         > >
>         > > Regards....
>         > >
>         > >
>         > >
>         > > Murat
>         > >
>         > >
>         > >
>         > >
>         > >
>         > > --
>         > > This message has been scanned for viruses and
>         > > dangerous content by MailScanner, and is
>         > > believed to be clean.
>         > >
>         > > _______________________________________________
>         > > gdal-dev mailing list
>         > > gdal-dev at lists.osgeo.org
>         > > http://lists.osgeo.org/mailman/listinfo/gdal-dev
>         > >
>         >
>         > --
>         > Best regards,
>         > Chaitanya kumar CH.
>         >
>         > +91-9494447584
>         > 17.2416N 80.1426E
>         >
>         > --
>         > This message has been scanned for viruses and
>         > dangerous content by MailScanner, and is
>         > believed to be clean.
>         
>         
>         Murat Beyhan
>         Jeofizik Yük. Müh.
>         T.C.
>         BAŞBAKANLIK
>         Afet ve Acil Durum Yönetimi Başkanlığı
>         Deprem Dairesi Başkanlığı
>         Eskişehir Yolu 12. Km. Lodumlu/Ankara
>         Tel:+90 (312) 2872680-1550
>         
>         
>         --
>         This message has been scanned for viruses and
>         dangerous content by MailScanner, and is
>         believed to be clean.
>         
> 
> 
> -- 
> Best regards,
> Chaitanya kumar CH.
> 
> +91-9494447584
> 17.2416N 80.1426E
> 
> -- 
> This message has been scanned for viruses and 
> dangerous content by MailScanner, and is 
> believed to be clean.



Murat BEYHAN 

Jeofizik Y.Müh.
T.C. Başbakanlık
Afet ve Acil Durum Yönetimi Başkanlığı
Deprem Dairesi Başkanlığı
Eskişehir Yolu 12. Km.
Lodumlu/ANKARA
Tel: 312 2872680-1556
email:beyhan at deprem.gov.tr




-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.



More information about the gdal-dev mailing list