72688

Find Long/Lat inside of polygon with MySQL 5.6

Question:

I have a locations table that has many rows of locations and data. There are 2 columns inside of that - Longitude and Latitude. I want to find records from that table that are found in a defined map polygon area.

I have a second table that I just created based on some tutorials called polyThing which has my defined boundary...

CREATE TABLE polyThing ( `ID` int auto_increment primary key, `boundary` polygon not null, `testarea` varchar(200) NOT NULL ); INSERT INTO polyThing (boundary, testarea) VALUES( PolygonFromText( 'POLYGON(( -114.018522 46.855932 , -113.997591 46.856030 , -113.997447 46.848626 , -114.018161 46.848824 , -114.018522 46.855932 ))' ), 'Test Area 1' );

I want to find records from locations that are inside this defined polygon, and I've been trying queries similar to this. I get 0 records no matter what I try.

SELECT * FROM locations WHERE ST_CONTAINS( (SELECT boundary FROM polyThing WHERE polyThing.testarea = 'Test Area 1') , Point(Longitude, Latitude))

Here is locations table and data:

CREATE TABLE `locations` ( `LocationID` int(11) NOT NULL, `Location` varchar(200) NOT NULL, `Longitude` varchar(200) NOT NULL, `Latitude` varchar(200) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT; INSERT INTO `locations` (`LocationID`, `Location`, `Longitude`, `Latitude`) VALUES (1, 'In the polygon', '-114.007191', '46.853019'), (2, 'In the polygon', '-114.003798', '46.851045'), (3, 'Not in', '-114.016934', '46.866098');

Love to get a nudge in the right direction.

Answer1:

I guess (Longitude, l.Latitude) are in locations?

<strong><a href="http://http://rextester.com/JSLYC95685" rel="nofollow">Sql DEMO:</a></strong>

SELECT l.*, ST_CONTAINS(p.boundary, POINT(l.Longitude, l.Latitude)) FROM locations l CROSS JOIN polyThing p; SELECT l.* FROM locations l JOIN polyThing p ON ST_CONTAINS(p.boundary, POINT(l.Longitude, l.Latitude)) AND p.testarea = 'Test Area 1';

<strong>OUTPUT</strong> <a href="https://i.stack.imgur.com/WbMkq.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/WbMkq.png" data-original="https://i.stack.imgur.com/WbMkq.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

Recommend

  • Output a property with PHP5 and method chaining
  • mysql not unique auto increment, primary key two fields
  • 2 count(*)+group by+having+join
  • String matching
  • DELETE statement issues within a trigger definition
  • Get Quarters StartDate and EndDate from Year
  • Memory usage of storing strings as varchar in MySQL
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • PostgreSQL primary key auto increment crashes in C++
  • How to find data from last week in MySQL
  • Cannot style mat-tab without ::ng-deep and !important
  • AlertDialog style when using setView()
  • Identifier too long in Oracle
  • Eliminate partial duplicate rows from result set
  • Why querying a date BC is changed to AD in Java?
  • Database structure design with variable amounts of fields
  • Why value captured by reference in lambda is broken? [duplicate]
  • Adding custom controls to a full screen movie
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Comma separated Values
  • Linker errors when using intrinsic function via function pointer
  • How to delete a row from a dynamic generate table using jquery?
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Error creating VM instance in Google Compute Engine
  • Hits per day in Google Big Query
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • Trying to get generic when generic is not available
  • Why joiner is not used after Sequence generator or Update statergy
  • how does django model after text[] in postgresql [duplicate]
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • How to stop GridView from loading again when I press back button?
  • LevelDB C iterator
  • Recursive/Hierarchical Query Using Postgres
  • Sorting a 2D array using the second column C++
  • How can i traverse a binary tree from right to left in java?
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal