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
– Ben Pearce, Commented Apr 23, 2013 at 2:26
did you try setting simple data for testing into table_name, like the data, used here: dev.mysql.com/doc/refman/4.1/en/relations-on-geometry-mbr.html. To check, that everything works as expected? — user4035
– user4035, Commented Apr 23, 2013 at 6:14

Gustek · Accepted Answer · 2013-04-23 22:04:12Z

4

+50

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, 2013 at 22:04

answered Apr 23, 2013 at 21:59

Gustek

3,7603 gold badges25 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eldjon Over a year ago

longitude is X and latitude is Y

eldjon Over a year ago

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.

david strachan · Accepted Answer · 2013-04-23 21:46:29Z

4

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;

edited Apr 23, 2013 at 21:46

answered Apr 23, 2013 at 11:51

david strachan

7,2282 gold badges25 silver badges33 bronze badges

1 Comment

Ben Pearce Over a year ago

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.

Collectives™ on Stack Overflow

Spatial query using my SQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related