MySQL Forums - GIS

How To: Storing and retrieving spatial data on MySQL server with Connector/Net (no replies)

Edwin Desouza — Wed, 17 Apr 2013 16:40:16 +0000

How To: Storing and retrieving spatial data on MySQL server with Connector/Net
https://blogs.oracle.com/MySqlOnWindows/entry/howto_storing_and_retrieving_spatial

How to add a point to existing linestrings in mysql? (no replies)

Prabu Raja — Thu, 11 Apr 2013 12:03:14 +0000

Is it possible to add a Point at the end of a Linestring in MySql?

For example I have a Linestring as

Linestring(1 2, 1 4, 1 6)

and I want to add a Point

Point(1 7)

at the end (output should be Linestring(1 2, 1 4, 1 6, 1 7))

Point Within Multipolygon (2 replies)

Jure Zakrajsek — Thu, 21 Mar 2013 15:03:44 +0000

Hello,

Im running mysql on synology NAS server version 5.1.49 - Source distribution that was installed on the system.

I have a database containing a multipolygon and i want to query if selected coordinates are within this multypoligon in a specific range of dates.
I have found the GISWithin function (http://forums.mysql.com/read.php?23,286574,286574#msg-286574) which works correctly, but it is working a lot slower than the build in Within function, since i guess it does not use the spatial index on my GIS field. Below is the explain result for my query. Im missing a possible_key field which is a spatial index for the multipolygon field.

EXPLAIN SELECT * FROM arhiv WHERE GISWithin(GEOMFROMTEXT( 'Point(120 10)' ) , field) AND obsdate BETWEEN '2010-01-01' AND '2013-01-01' ORDER BY obsdate ASC
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE arhiv range obsdate obsdate 8 NULL 180659 Using where

The build in WITHIN function seems to give false results, it looks like it uses MBR calculations.

Is there a way to test if the GIS functions in this distribution work in a correct way and not with MBR calculations.
If my mysql does not have the corrected within function is there any way to implement it into my distribution? Im very limited on building it from source.

Thank you for any help
Best regards
Jure

ST_Intersect (no replies)

Nikos M — Mon, 26 Nov 2012 11:51:53 +0000

Hello everyone,
I am having some troubles with the ST_Intersects on MySQL 5.6.7

I have two geometries: a polygon and a line with three points, two of them lie inside the polygon and the third is outside.

To my understanding, ST_Intersects should return 1 but it doesn't...
Here is an example query:

Select ST_Intersects(GeomFromText('LineString(40.703782 0.58101475, 40.7037898 0.58075726, 40.70378574 0.58051049)'), GeomFromText('POLYGON((40.703266 0.580561,40.704286 0.580561,40.704286 0.582181,40.703266 0.582181,40.703266 0.580561))'))

Any ideas?

join on (1 reply)

Jacek Gancarson — Tue, 16 Oct 2012 23:34:39 +0000

Hello,

I have to select the posts from the table `Dammreg` under condition that the string in the DNAM field includes the string from the field Damm_vk.NAMN
I have used the following sentence:

SELECT * FROM Dammreg INNER JOIN Dammreg ON Dammreg.DNAMN LIKE CONCAT('%', Damm_vk.NAMN, '%');

and received the error message: #1066 - Not unique table/alias: 'Dammreg'.

I used also the sentence:

SELECT * FROM Dammreg
JOIN TABLE Dammreg ON Dammreg.DNAMN LIKE '%'+ Damm_vk.NAMN +'%' with no result.

With this sentence:
SELECT * FROM Dammreg
JOIN TABLE Dammreg ON Dammreg.DNAMN LIKE '%' + @Damm_vk.NAMN + '%'
I receive the message: #1046 - No database selected.

Erlier I read the thread about error:#1066 and some other threads but still can not find what is wrong.

Server version: 5.1.63-0+squeeze1
Many thanks in advance

Names of polygons (2 replies)

Martin Jones — Sun, 09 Sep 2012 12:05:58 +0000

I have managed to create tables with polygon data (Eastings and Northings) in Id,GEOMETRYCOLLECTION data types,

How do I add a name to each polygon and then get the name back when I have a "Within" result

Many thanks
Martin

Cannot get geometry object from data you send to the GEOMETRY field (1 reply)

Crackie Maggie — Fri, 07 Sep 2012 20:20:23 +0000

Hi All,

I kept getting error:Cannot get geometry object from data you send to the GEOMETRY field

We have a POLYGON table TBL_BDY which has two columns :CODE, BDY .BDY is meant to be like a geometry column except the data type is text.

In the BDY column : the format of the latitude longitude is arranged as : 10 -10|1 -1|10 -10|.....and so on...it is seperated by a pipeline.

My effort is to try to spatialize the table.

I created a replicated table with an additional column of GEOMETRY.

CREATE TABLE tbl_bdy_geom
(CODE VARCHAR(4),
BDY TEXT,
GEOLOC GEOMETRY);

I then try to insert the table by replacing theipelines with comma...but I ran into error:Cannot get geometry object from data you send to the GEOMETRY field

insert into tbl_bdy_geom (code,bdy,geoloc)
select code,bdy,polyFromText(REPLACE(`bdy`, '|', ',')) from `tbl_bdy`;

Any help is much appreciated!

Google Maps API: Geocoding Addresses with PHP/MySQL (no replies)

Edwin Desouza — Mon, 23 Jul 2012 23:18:47 +0000

Google Maps API: Geocoding Addresses with PHP/MySQL
https://developers.google.com/maps/articles/phpsqlgeocode
https://developers.google.com/maps/articles/phpsqlsearch_v3
https://developers.google.com/maps/articles/phpsqlajax_v3

GIS development status (1 reply)

P R — Tue, 12 Jun 2012 02:11:19 +0000

whats the status of the development work dealing with the mysql spatial/GIS extensions? Its june 2012, and many of the posts here in the GIS forum look very old. The info. on the mysql forge wiki also looks dated. Has work on the GIS extensions stalled? is anyone still actively working on expanding the GIS functionality in mysql?

GIS dev branch on bazaar/launchpad? (1 reply)

P R — Tue, 12 Jun 2012 02:14:52 +0000

Im trying to get the bleeding-edge GIS code for mysql (Im also considering contributing, since I need some of the gis stuff that postGIS has and mysql lacks).

The mysql-forge wiki says that all the GIS code has been merged into the trunk, but doesnt mention anything about any specific dev branches that focus on the GIS stuff.

Can anyone please provide info on which branches I should be looking at for GIS?

Implementing efficient Geo IP location system in MySQL (no replies)

Edwin Desouza — Fri, 01 Jun 2012 19:01:14 +0000

Implementing efficient Geo IP location system in MySQL
http://www.dbasquare.com/2012/06/01/implementing-efficient-geo-ip-location-system-in-mysql/

Multi-dimensional data in SPACIAL types (1 reply)

John Nahlen — Tue, 12 Jun 2012 02:28:43 +0000

Hi guys,

I am wanting to create a schema that stores a chessboard. Seeing how a chessboard is somewhat like a grid or table (a two-dimensional object), I thought that we could use each point on the grid to store the piece value, and then index the values at each specific point in the 8x8 grid.

Basically the X-axis would represent ranks 1-8 in chess and the Y-axis would represent files A-H in chess.

This is not a valid query, but I thought maybe something like this might be what I wanted:

# board[0][0] would represent A8, board[7][7] would represent H1, etc
# there would be 13 possible values: K Q R B N P k q r b n p -
SELECT * FROM mytable WHERE board[0][0] = 'r';

So I was wondering if anyone had any ideas they could share. From what I can see, the GEOMETRY type might be my best bet, but I spent a bit of time on Google and found no examples of its use.

Thanks,

- John

Strange behavior with contains function and bounding rectangle (1 reply)

Carlos Quiros — Tue, 06 Dec 2011 20:11:55 +0000

Hi,

I imported a ESRI shapefile into mysql using two methods. GDAL (ogr2ogr) and Barend Kobben's shp2mysql. The shape files contains three countries (Kenya, Uganda and Tanzania). The insert of the geometry for each shape went without warnings or errors (see script link below).

Now, when I try to find out if a point is contained by a geometry using the function 'contains' in some cases returns two records when It should be just one and seems to happen because the bound rectangle. See this example image:

http://www.qlands.com/other_files/br.jpg

If I choose points that are contained within two bound rectangles (red dot in example image), the sql returns two records, if I use a point that is just contained by one bound rectangle (blue dot in example image) the sql returns positively just one . As far as I know the original shape file does not have bound rectangles as part of the shapes.

How can I ignore the bounding rectangle so I can select a point that is just inside the geometry of a shape? I thought that was the difference between 'contains' and 'MBRcontains'.

I am using an SQL like:

SELECT OGR_FID FROM test2 where contains(ogc_geom,GeomFromText('POINT(32.17 -1.52)'));

The table is:

+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| OGR_FID | int(11) | NO | PRI | NULL | auto_increment |
| ogc_geom | geometry | NO | | NULL | |
+----------+----------+------+-----+---------+----------------+

You can get the insert script for here:

http://www.qlands.com/other_files/test.sql (348k)

Many thanks for any help!

Carlos.

Polygon search fails... and fails... and fails... (no replies)

Phil Petree — Sat, 19 Nov 2011 15:12:04 +0000

In my table I have three geospatial fields:
`lat` decimal(12,7) NOT NULL,
`lon` decimal(12,7) NOT NULL,
`location` point NOT NULL,

And three geospatial indices:
KEY `lat` (`lat`),
KEY `lon` (`lon`),
SPATIAL KEY `location` (`location`)

I then have a trigger defined as 'on before update' which sets the `location` from the lat/lon pair like this:
FOR EACH ROW SET NEW.location = PointFromText(CONCAT('POINT(',NEW.lat,' ',NEW.lon,')'))

Here's my query:
SET @bbox = 'POLYGON((32.70 -117.16, 47.97 -122.19, 44.80 -68.77, 25.77 -80.19 ))';
SELECT * , AsText( location )
FROM geo_table
WHERE Intersects( location, GeomFromText( @bbox ) );

Running this query through phpMyAdmin I get the following message for both SQL statements:
# MySQL returned an empty result set (i.e. zero rows).

The data points shown in the above SET @bbox are from 4 corners of the US (entered clockwise) and should return 99% of all points but returns nothing. Here are the coordinates:
San Diego, CA: +32.70 -117.16
Seatle, WA: 47.97 -122.19
Bangor, Maine: 44.80 -68.77
Miami, FL: 25.77 -80.19

I have also tried this against over 3500 polygons from the NWS CAP 1.1 feed and I get nothing! I figure I must be doing something wrong but, for the life of me, I can't figure out what it is!

Any suggestions?

Do spatial functions still use MBR? (3 replies)

Sunil Padda — Wed, 07 Dec 2011 20:01:42 +0000

Hi,

I was reading through several posts here, along with blogs/posts in the web about GIS capabilities of MySQL. A lot of these posts state that MySQL uses the MBR instead of the entire polygon to do a spatial search. Is it still the case? If this has been fixed, please point me to the version of MySQL with which this support has been added.
Also, it would be great if you can point me to a page showing the supported GIS spatial functions.

Another question is on InnoDB support for spatial data. Reading through the page (http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html), I see that though InnoDB can be used to store spatial data, spatial indexes are not yet supported.

Thanks,
Sunil

How link to longitude & latitude fields (no replies)

Jim M — Wed, 26 Oct 2011 13:12:53 +0000

NEWBIE GIS help!

I am attempting to relate (link) to the latitude/longitude fields stored in spatial MYSQL5.1 DBMS created with GIS program (Manifold System) by using Filemaker or other DB programs to access. I have the FM ODBC driver (Actual Technologies) and can access the MYSQL tables. The tables look odd!

The problem I have is the tables in MYSQL5.1 do not appear to be normal looking tables to a Novice when I view in Workbench on the server. I assume this is because MYSQL manages the data how it wants for efficiency. They look normal when viewing in my GIS program. So I am unable to identify the lon/lat fields to create the external relationship.

What is the trick? Do I need to create a new table upload it to MYSQL via my GIS program & configure certain field types, then link to that new table?

My goal is to use my GIS program locational data (lat/lon) linked (related) to my Filemaker data table (lat/lon).

Any tips would be most helpful. Many thanks.

- Jim M

MBR within not accurate (no replies)

newbie Shai — Sun, 16 Oct 2011 04:41:04 +0000

Dear All,
I have been using the MBRWithin function for quite a lot of times. Suddenly I notice on google map this 'POINT(101.11857 4.34475)') is out of the geo fence which I specify but it still give a value of 1 in mysql any reason or tweaking need to be done?

SELECT MBRWithin(GeomFromText('POINT(101.11857 4.34475)'),GeomFromText('POLYGON((101.12112522125244 4.3531723687957164,101.11846446990967 4.351417913665312,101.13138198852539 4.336397898951581,101.13477230072021 4.33211863778494,101.14065170288086 4.321933898868271,101.14992141723633 4.306699328215635,101.15455627441406 4.30978050198082,101.1397933959961 4.334600612212089,101.12112522125244 4.3531723687957164,101.12112522125244 4.3531723687957164))')) As geoFenceStatus

Finding WGS-84 locations within a specfied distance of each other (no replies)

Lloyd Borrett — Sat, 08 Oct 2011 22:03:04 +0000

G'day,

I have a few MySQL databases with WGS-84 datum based Lat and Lon coordinates stored in them. I would like to be able to be able to query each database to find any points within 'x' metres of each other. This would make it easier to locate possible duplicate points.

I came across a MySQL function at http://forge.mysql.com/tools/tool.php?id=222 to calculate the distance between two points using Vincenty Distance WGS-84 algorithm.

So the query would have to get each point, then calculate the distance from it to every other point, and select those less than or equal to the specified distance, and then do the same for every other point.

Would anyone out there be able to construct a MySQL query to achieve this based on the above distance function?

Then we'd be able to use phpMyAdmin to run the query and learn more about the points stored.

Best Regards, Lloyd Borrett.

Can Not Create Geospatial Table (2 replies)

John Schattel — Thu, 29 Sep 2011 16:24:49 +0000

I am trying to create a geospatial table using the following:

CREATE TABLE geo1 (g GEOMETRY);

I get the following error:

ERROR 1064: You have an error in your SQL syntax near 'GEOMETRY)' at line 1

Does this mean that our version (see query below) of MySQL doesn't support geospatial tables?

mysql> select version();
+-----------+
| version() |
+-----------+
| 3.23.58 |
+-----------+
1 row in set (0.00 sec)

if so, is there a way to add the geospatial extension?

MySQL Spatial performance (no replies)

Lukasz Nowak — Fri, 26 Aug 2011 07:20:45 +0000

Hi,
I've checked query execution times using MBRContains function. I have 1mln random records (points) put on the surface 500x500. The query execution times are:
1) Area 50x50 | 0,11sec | 9963 records
2) 100x100 | 0,35 sec | 40013
3) 200x200 | 1,176 sec | 160009
4) 300x300 | 0,482 sec | 359126
5) 400x400 | 0,765 sec | 639874

Why times in 4) and 5) are shorter than in 3). The areas and records number are greater...

Sorry for my english

configure flag (2 replies)

Jeff Rossi — Fri, 29 Jul 2011 11:35:49 +0000

I'm on slackware 13.1 and I previously had to rebuild mysql to enable profiling.

Is there a configure flag to enable the spatial extensions?

I've been running some rails code and the queries look ok in the console but my field is always null. I figured I may need to enable the spatial extensions I'm running mysql 5.1.46

Poi based on lat itude and longitude (no replies)

newbie Shai — Wed, 25 May 2011 02:07:16 +0000

Dear All,
I have a big table of poi's based on latitude and longitude. So then based on a given latitude and longitude I would like to find the nearest point in my poi table. Can I use some mysql functionality to do to be best optimize way. The problem I am currently doing it in php using the standard method as below

$distance = (3958*3.1415926*sqrt(($lat-floatval($row['lat']))*($lat-floatval($row['lat'])) + cos($lat/57.29578)*cos(floatval($row['lat'])/57.29578)*($long-floatval($row['long']))*($long-floatval($row['long'])))/180);

Below is my table.

CREATE TABLE IF NOT EXISTS `poi` (
`poiID` int(11) NOT NULL auto_increment,
`type` varchar(50) NOT NULL,
`locationName` varchar(200) NOT NULL,
`state` varchar(50) NOT NULL,
`city` varchar(50) NOT NULL,
`lat` float NOT NULL,
`long` float NOT NULL,
PRIMARY KEY (`poiID`),
KEY `lat` (`lat`,`long`),
KEY `lat_2` (`lat`),
KEY `long` (`long`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=201046 ;

GIS functions in 5.1.35 dev version - when to be added to prod release? (1 reply)

László Kovács — Mon, 30 May 2011 22:46:15 +0000

Hi

I was very happy when I discovered that there's a version of MySQL that includes a number of long-awaited GIS functions. I'd be even happier if these developments were included in the prod release. Is it known if and when this is going to happen?

Thx,
Laszlo

Use My SQL GIS db for routing (no replies)

K jo — Tue, 17 May 2011 15:15:07 +0000

I am doing some research and trying to figure out if it possible to use MYSQL to find cities in a route or within a radius of that route.

If I am going from point A to point B. I want find all cities on the route between city A and city B. Is it possible?

If I can get all cities can I filter by population or by radius of the route ?

I am not even sure if this is doable with any spatial db but wanted to bounce this off others.

Thanks./

contains/within functions still using MBR??!?! (no replies)

P R — Mon, 11 Apr 2011 03:29:28 +0000

I just installed a development snapshot of 5.6 from the labs.
It seems that the gis functions are still using mbr's, can someone verify this?
what happened to the holyfoot fixes? where are they? how can I get them?

I have a 'world countries' database table, with a geometry spatial column for country borders. the geometry column contains polygon & multipolygon types. Performing queries to find out in which country a point lies will INCORRECTLY return multiple results; its obvious that mbr's are being used.

example:
SELECT * FROM world_countries WHERE Contains(SHAPE,GeomFromText('POINT(-6.289999961853027 36.5099983215332)'))
this point is located in southern spain, yet the query returns spain, algeria, portugal, even the U.S., due to the dateline wrap-around.
I've already verified that the polygon data in the spatial column is correct, so the problem is that mysql's gis functions are unable to accurately determine which polygon a point lies within.

anyway, can someone please help me out with this?

Work with geodetic coordinates (1 reply)

Samuel Rabini — Sat, 19 Mar 2011 14:21:22 +0000

Hi,
I'm working on MySQL 5.0.91-enterprise-gpl-log.

I need to enjoy spatial functions using earth coordinates.
How is it possible to specify the reference plan of my coordinates?

Thanks in advance,
Samuel

Web Game using MySql GIS extensions (no replies)

Marco Afonso — Wed, 16 Mar 2011 17:57:52 +0000

Hi All,

We would like to announce the tester release of Diplomatic Wars.

Diplomatic Wars is a free multiplayer strategy game using an unique worldwide interactive map using OpenLayers + MySql + Geoserver

We are giving a try to MySql Spatial Extensions to support game geometry data.

Please have a try.

Register at http://www.diplomaticwars.com/

SELECT * WITH(POLYGON with HOLES) : Error (no replies)

Boni Gopalan — Mon, 14 Mar 2011 09:36:20 +0000

The test dataset contains 1000 random rectangles within X  [0,10000], Y  [0,10000]. I would like to query for rectangles within a polygon consisting of an outer ring and a hole within.

SELECT COUNT(*) FROM tile WHERE
within(tile.LOCATION,
GeomFromText(
'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1))'
)
) = 1

Result: 1000 [As expected]

SELECT COUNT(*) FROM tile WHERE
within(tile.LOCATION,
GeomFromText(
'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
)
) = 1

Result: 248 [As expected]

SELECT COUNT(*) FROM tile WHERE
within(tile.LOCATION,
GeomFromText(
'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
)
) = 1

Result: 1000 [Expected : 752 since 248 falls inside the hole]

If I run

SELECT COUNT(*) FROM tile WHERE
within(tile.LOCATION,
GeomFromText(
'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1)'
)
) = 1
AND
within(tile.LOCATION,
GeomFromText(
'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
)
) = 0

Then result is 0

Where as

SELECT COUNT(*) FROM tile WHERE
within(tile.LOCATION,
GeomFromText(
'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
)
) = 1
AND
within(tile.LOCATION,
GeomFromText(
'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
)
) = 0

Result is 752, exactly what I’d expect!!

Can anyone help me understand this behavior?

Database - points of latitude and longitude. (no replies)

Marylka Pieroszkiewicz — Tue, 01 Mar 2011 21:42:34 +0000

I need to create a database with GIS spatial extensions. I need points of latitude and longitude. I'm creating an application in the Adobe Flash Builder (Flex 4), which specifies uses google map api. I want to use Adobe ColdFusion 9 was combined with a MySQL database. The database will store information: id, name, points of latitude and longitude.

Please help how to create such a database.
Thank you!

Marylka

Add GIS data types to a classic mysql server 5.0 (no replies)

Yema Mario — Thu, 24 Feb 2011 16:23:25 +0000

Hello!
Please, i would like to know if there is a GIS patch or a GIS plugin for mysql server.
My server is running on Debian etch OS. Now, i would like to add this plugin in order to insert GIS data like Point, Rectangle...
If impossible, please, what must i do?
Thanks!