[gdal-dev] drawing line using OGR and mysql in mapserver
Murat Beyhan
beyhan at deprem.gov.tr
Wed Feb 29 09:34:39 EST 2012
Chaitanya,
here is the structure of table on database
sfault :
eq_id varchar(14)
lat1 decimal(7,4)
lon1 decimal(7,4)
lat2 decimal(7,4)
lon2 decimal(7,4)
lineview :
eq_id varchar(14)
geom double
it seams geom as double
I thing it should be geometry isn't it?
may be I have to change structure of lineview by changing following
Roberts' suggestion to create view on database
create view lineview as select eq_id, "LINESTRING(" + lon1 + " " + lat1
+ "," + lon2 + " " + lat2 + ")" as geom from sfault
but I'm not sure for the line.
VTR for points, I have tested and works well
but for line still could not achieved yet, please give me another idea.
here is test.vrt file
<OGRVRTDataSource>
<OGRVRTLayer name="sta">
<SrcDataSource>MYSQL:stations,user=root,password=mysql_passwd,host=localhost,port=3306,tables=sta</SrcDataSource>
<SrcSQL>SELECT sta_id as name,type,latitude,longitude from sta where
type like "%SMACH%"</SrcSQL>
<GeometryType>wkbPoint</GeometryType>
<GeometryField encoding="PointFromColumns" x="longitude" y="latitude"/>
</OGRVRTLayer>
</OGRVRTDataSource>
and following code gives successful result as you see
ogrinfo -ro -al test.vrt
INFO: Open of `test.vrt' using driver `VRT' successful.
Layer name: sta
Geometry: Point
Feature Count: 12
Layer SRS WKT:
(unknown)
name: String (12.0)
type: String (24.0)
latitude: Real (5.4)
longitude: Real (5.4)
OGRFeature(sta):0
name (String) = 0617
type (String) = SMACH
latitude (Real) = 40.4569
longitude (Real) = 32.6319
POINT (32.631900000000002 40.456899999999997)
OGRFeature(sta):1
name (String) = 0618
type (String) = SMACH
latitude (Real) = 40.4798
longitude (Real) = 32.4555
POINT (32.4555 40.479799999999997)
.
.
.
.
As you see this works well.
I hope you can catch point what is wrong or lack of on my works....
Thanks again
On Tue, 2012-02-28 at 18:08 +0530, Chaitanya kumar CH wrote:
> Murat,
>
>
> I see that your view, lineview, doesn't give the expected geometry
> type with ogrinfo. It is showing the datatype as Real. Check if the
> datatype of geom is actually string/text.
>
> On Tue, Feb 28, 2012 at 12:49 PM, Murat Beyhan <beyhan at deprem.gov.tr>
> wrote:
> 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.
>
>
>
>
>
>
> --
> 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