[gdal-dev] drawing line using OGR and mysql in mapserver
Murat Beyhan
beyhan at deprem.gov.tr
Fri Mar 2 08:09:21 EST 2012
Here is
also I could store data to mysql as follows
INSERT INTO geom set eq_id='20111023101120',
fault=GeomFromText('linestring(38.6636 43.8547,38.6952 43.0851)')
SQL result
Host: localhost
Database: dynamic
Generation Time: Mar 02, 2012 at 03:04 PM
Generated by: phpMyAdmin 3.3.10 / MySQL 5.1.55
SQL query: SELECT * FROM `geom` WHERE 1 LIMIT 0, 30 ;
Rows: 2
eq_id
fault
20140101010101
[GEOMETRY - 45B]
20111023101120
[GEOMETRY - 45B]
I hope you can get point.
actually geom tables seems like a table which I store shape file in
mysql using shp2mysql.pl but database has a table spatial_ref_sys and it
has this kind of structure as follows
spatial_ref_sys
Field
Type
Null
Default
Comments
SRID
int(11)
No
0
AUTH_NAME
text
Yes
NULL
AUTH_SRID
int(11)
Yes
NULL
SRTEXT
text
Yes
NULL
any way What is my progress :)
murat
On Fri, 2012-03-02 at 18:32 +0530, Chaitanya kumar CH wrote:
> Murat,
>
> Can you show a sample output of your view?
> SELECT * FROM geom;
>
> On Fri, Mar 2, 2012 at 6:12 PM, Murat Beyhan <beyhan at deprem.gov.tr>
> wrote:
>
>
>
>
> Dear Chaitanya,
>
> I see what you explain first I convert lat lon data to string
> then I
> concatenate them to string again. But I would like to ask
> I have already add data to mysql table as follows is this also
> correct
> because when I browse table it seems it store data in geometry
> field
> but mapserver gives error
> Unable to identify source field 'fault' for geometry.
> any way
> I will try to convert data first then I can return back you.
> Regards...
>
> ps.
>
> ogrinfo
> MYSQL:dynamic,user=root,password=mysql_123,host=localhost,port=3306,tables=geom
> INFO: Open of
> `MYSQL:dynamic,user=root,password=mysql_123,host=localhost,port=3306,tables=geom'
> using driver `MySQL' successful.
> 1: geom (None)
>
>
>
>
> it say none in geom
> may be first I have to do what you say
> thanks
> Murat
>
>
> On Fri, 2012-03-02 at 18:05 +0530, Chaitanya kumar CH wrote:
> > Murat,
> >
> > Put aside linestrings and geometries. First you need to have
> a wkt
> > representation of your line segment. Your MySQL query is
> returning a
> > number. That should be an indicator that your query was
> wrong. You
> > were using the wrong operator. You need to convert the
> latitude and
> > longitude values to string and concatenate the them with the
> other
> > strings to make up the wkt of linestring geometries.
> Otherwise, MySQL
> > is just adding the string and decimal values mathematically.
> >
> > Use the CONVERT function as described in the website:
> >
> http://www.geeksengine.com/database/single-row-functions/conversion-functions.php
> >
> > On Fri, Mar 2, 2012 at 5:38 PM, Murat Beyhan
> <beyhan at deprem.gov.tr>
> > wrote:
> > Chaitanya,
> >
> > I could not do this using sql sorry.
> >
> > But I have an another idea is it good way to store
> these data
> > as a
> > spatial database in MySql.
> > I try this but when I insert data to mysql table
> > I have problem .
> > I have created a table
> > as follows
> >
> > eq_id varchar(14)
> > fault linestring
> >
> > then I will insert data (if I success how to do
> that)
> > then I will use this table from mapserver.
> > is this proper way.
> > Or how to declare geom column as string at sql
> statement as
> > follows
> >
> > create view lineview as select eq_id, "LINESTRING("
> + lon1 +
> > " " + lat1
> > + "," + lon2 + " " + lat2 + ")" as geom from sfault
> >
> >
> >
> > please give your idea...
> >
> >
> >
> > On Thu, 2012-03-01 at 14:40 +0530, Chaitanya kumar
> CH wrote:
> > > Murat,
> > >
> > >
> > > It's not working. the geom field should not be
> 'Real'. It
> > should be
> > > 'String'. Try something else other than the '+'
> operator in
> > the SQL
> > > syntax to create the geom field in the view.
> > >
> > > On Thu, Mar 1, 2012 at 12:48 PM, Murat Beyhan
> > <beyhan at deprem.gov.tr>
> > > wrote:
> > > Chaitanya,
> > >
> > > I have had a progress about VRT data
> access
> > > but I afraid something wrong in
> mapserver .map file.
> > >
> > > here is the test.vrt file and I use in map
> file with
> > > connection ogr
> > > functionality. :
> > >
> > > <OGRVRTDataSource>
> > > <OGRVRTLayer name="dynamic">
> > >
> >
> <SrcDataSource>MYSQL:dynamic,user=root,password=mysql_passwd,host=localhost,port=3306,tables=lineview</SrcDataSource>
> > > <SrcSql>select eq_id, geom from
> lineview</SrcSql>
> > > <GeometryType>wkbLineString</GeometryType>
> > > <GeometryField encoding="WKT"
> field="geom"/>
> > >
> > > </OGRVRTLayer>
> > > </OGRVRTDataSource>
> > >
> > > then the command
> > >
> > >
> > > [murat at localhost stations]$ ogrinfo -ro
> -al test.vrt
> > > INFO: Open of `test.vrt'
> > > using driver `VRT' successful.
> > >
> > >
> > > Layer name: dynamic
> > > Geometry: Line String
> > > Feature Count: 2
> > > Layer SRS WKT:
> > > (unknown)
> > >
> > > eq_id: String (42.0)
> > > geom: Real (23.0)
> > > OGRFeature(dynamic):0
> > > eq_id (String) = 20111023101120
> > > geom (Real) = 164
> > >
> > > OGRFeature(dynamic):1
> > > eq_id (String) = 20100308023229
> > > geom (Real) = 163
> > >
> > >
> > >
> > > it works now but mapserver gives an error
> as
> > follows:
> > >
> > > Server error!
> > > The server encountered an internal error
> and was
> > unable to
> > > complete your
> > > request.
> > >
> > > Error message:
> > > Premature end of script headers: mapserv
> > >
> > > If you think this is a server error,
> please contact
> > the
> > > webmaster.
> > >
> > >
> > > Error 500
> > > 10.14.0.67
> > > Apache/2.2.14 (Mandriva
> Linux/PREFORK-1.6mdv2010.0)
> > >
> > >
> > >
> > > I think I will solve it with your help.
> > >
> > > Many thanks
> > >
> > > Murat
> > >
> > >
> > > On Thu, 2012-03-01 at 09:22 +0530,
> Chaitanya kumar
> > CH wrote:
> > > > Murat,
> > > >
> > > > You should check MySQL's SQL syntax to
> concatenate
> > strings
> > > and
> > > > decimals to produce a string.
> > > >
> > > > On Wednesday, February 29, 2012, Murat
> Beyhan
> > > <beyhan at deprem.gov.tr>
> > > > wrote:
> > > > > 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)
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > > --
> > > > 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.
> >
> >
> >
> >
> >
> > --
> > 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