Spatial query using my SQL

Question

I am writing from two varChar formatted columns named 'lattitude' and 'longitude' to a Point formatted column named 'coordinate' using the statement below.

"UPDATE table_name SET coordinate = PointFromText(CONCAT('POINT(',table_name.longitude,' ',table_name.lattitude,')'))"

I attempting to do a spatial query on the coordinate column using the following statement.

SELECT id , coordinate FROM table_name WHERE MBRContains(GeomFromText('Polygon(-126.728566 49.226434, -123.652395 23.586457,-56.679738 23.908252,-53.076223 55.243002)'), coordinate)

According to this tool the polygon in my query covers the entire U.S. I know there are points in my table that fall in the U.S. but I am still not getting any results (note I'm getting a null result not an error). Any ideas as to what I'm doing wrong?

UPDATE. Below is an updated attempt at executing my query per the suggestion below.

SET @g1 = GeomFromText('Polygon((23.586457 -123.652395,23.908252 -56.679738,55.243002 -53.076223,55.243002 -53.076223,23.586457 -123.652395))');

SELECT id , coordinate FROM table_name WHERE MBRContains(@g1, coordinate)

I tried adding the first coordinate again at the and and still got the same result. — Ben Pearce, Apr 23 '13 at 02:26
did you try setting simple data for testing into table_name, like the data, used here: http://dev.mysql.com/doc/refman/4.1/en/relations-on-geometry-mbr.html. To check, that everything works as expected? — user4035, Apr 23 '13 at 06:14

david strachan · Answer 1 · 2013-04-23T21:46:29.327

As you are dealing with coordinates you have the lat lng coodinates crossed.

I created a Google Map using the coordinates below(reversing the order of your coordinates in the Question.

new google.maps.LatLng(49.226434,-126.728566),
new google.maps.LatLng(23.586457,-123.652395),
new google.maps.LatLng(23.908252,-56.679738),
new google.maps.LatLng(55.243002,-53.076223)

Polygon

Google Maps does not require polygon to be closed.

According to documentation MBRs require the polygon to be closed.

mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');

EDIT In answer to comment Have you the correct CHARSET ?

Typical spatial table

CREATE TABLE IF NOT EXISTS `table_name` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `coordinate` geometry NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Ensure CHARSET=utf8;

Hi David, thanks for your suggestion. I have attempted to implemented (demonstrated in the edit above) but I'm still not getting any results. I wonder if I'm not writing to my coordinate column properly. I should probably mention that when I look at my coordinate column in phpMyAdmin, it looks like a bunch of encoded characters ( e.x. ��/=�[J@) i.e. not human readable. Not sure if this is normal. — Ben Pearce, Apr 23 '13 at 20:48

Gustek · Accepted Answer · 2013-04-23T22:04:12.423

4

SELECT id , coordinate FROM table_name WHERE MBRContains(GeomFromText('Polygon((-126.728566 49.226434, -123.652395 23.586457,-56.679738 23.908252,-53.076223 55.243002, -126.728566 49.226434))'), coordinate)

This should work. The problem is that Polygon coordinates should be in double parenthesis. It has something to do about interior rings (polygons). Plus as David noticed You need to close polygon.

PS: I noticed You use longitude as X and latitude as Y when this should be other way around. X are horizontal and Y vertical.

edited Apr 23 '13 at 22:04

answered Apr 23 '13 at 21:59

Gustek

3,680
2
22
36

longitude is X and latitude is Y – eldjon Apr 15 '15 at 09:33
The X is the coordinate on the horizontal axis so you draw a vertical line from the point to the X-axis to find the X coordinate and the other way around with Y. – eldjon Apr 15 '15 at 09:39

Spatial query using my SQL

2 Answers2