sql - MySQL Point in Polygon Queries and DB Setup -


i trying set table consists of regions polygon. want able query table find whether given point lies in of stored regions or not.

i read mysql spatial extensions provide 2 types of functions operate geometries:

  • st_* operating on object shapes
  • mbr* operating on minimum bounding rectangles

i tried following worked in this post. here's how set table:

create table `region` ( `id` int(11) unsigned not null auto_increment, `name` varchar(50) not null, `rpolygon` polygon not null, primary key (`id`) ) engine=innodb default charset=utf8;

i inserted few records in table after getting polygon definitions qgis. learned inserting spatial records table here. here's how inserted record in table

set @g= st_geomfromtext('polygon ((-117.84293016891291472 33.64825334189644224, -117.8428418279120109 33.64599663087331294, -117.84048874488789238 33.64467151585973426, -117.84002294688312418 33.64517746886491523, -117.83983020288114574 33.64549067786812486, -117.83981414088098916 33.64605284787388939, -117.83983823388122403 33.64646242887808825, -117.84012734988419879 33.64708081588442212, -117.84053693088839054 33.64743417988804453, -117.84130790689629009 33.6479401328932326, -117.84199054190328582 33.64815696989545302, -117.84243224690781915 33.64822121789610776, -117.84293016891291472 33.64825334189644224))'); insert region  values(1, 'north-east', @g);

just sanity check, tried running point in polygon query using various methods - mbrcontains, st_contains etc, of result in no results or null. below sample query tried:

select * region st_contains(rpolygon, st_geomfromtext('point(33.646201 -117.841949)'));

i not sure going wrong. storing values incorrectly, set geo table in different way?, or querying incorrectly. couldn't find tutorials online or examples follow such setup. pointers helpful!

damn! feel pretty stupid! order of coordinates wrong in query define point.

anyway. hope serves short tutorial wanting started point-in-polygon queries mysql


-

Comments

Post a Comment

Popular posts from this blog

PySide and Qt Properties: Connecting signals from Python to QML -

i trying run simple pyside appliccation design made in qml. call function in qml python in separated thread. i have following qml view: import qt 4.7 rectangle { visible: true width: 100; height: 100 color: "lightgrey" text { id: datestr objectname: "datestr" x: 10 y: 10 text: "init text" font.pixelsize: 12 function updatedata(text) { datestr.text = qstr(text) console.log("you said: " + text) } } } here application: some imports.... #!/usr/bin/python import sys import time pyside import qtdeclarative, qtcore pyside.qtgui import qapplication pyside.qtdeclarative import qdeclarativeview the qthread worker.... #worker thread class worker(qtcore.qthread): updateprogress = qtcore.signal(int) def __init__(self, child): qtcore.qthread.__init__(self) self.child = child def run(self):
Read more

c# - DevExpress.Wpf.Grid.InfiniteGridSizeException was unhandled -

Image
when using devexpress, see error: devexpress.wpf.grid.infinitegridsizeexception unhandled message="by default, infinite grid height not allowed since grid rows rendered , hence grid work slowly. fix issue, should place grid container give finite height grid, or should manually specify grid's height or maxheight. note can avoid exception setting gridcontrol.allowinfinitegridsize static property true, in case grid run slowly." the problem dxgrid has infinite height. to fix, set height non-infinite. snoop absolutely invaluable this: if "height" xaml element infinite (i.e. 0 or nan ), can set using one of following: option 1: height="{binding path=actualheight, relativesource={relativesource mode=findancestor, ancestortype=uielement}}" option 2: verticalalignment="stretch" option 3: height="auto" hint: use verticalalignment="stretch" if child of grid <rowdefinition height=
Read more

scala - 'wrong top statement declaration' when using slick in IntelliJ -

Image
i'm porting project scala 2.9 2.10, therefore have use slick instead of scalaquery. i'm using slick 2.1.0 since supports ms access. according this tutorial , upgrade guide changed robs object class , added val robs : why error message wrong top statement declaration , how rid of it? edit: i'm new scala... according this question seems can't put val outside of methods or classes, right? code above directly in packge. right approach slick then? should move code in class or trait? according this changed val robs = tablequery[robs] to object robs extends tablequery(new robs(_)) {} . no warnings or errors anymore. :)
Read more