0

I am pulling State geometry data from a database (populated from Shapefiles) to find what state a coordinate pair is in. I was originally using GeoAlchemy, but tens of thousands of queries to determine the state is quite expensive.

def get_state_from_coordinates(self, db_session, long, lat):
    return db_session.query(States)\
        .filter(func.ST_Contains(States.geom, func.ST_PointFromText('POINT({0} {1})'.format(long, lat), 4269)))\
        .first()

So my current solution is to pull all of the state data into my application and do the lookup locally. I wanted to spike out a lookup and have gotten stuck.

states = db_session.query(States).all()

for state in states:
    point = Point(-81.4406293, 30.323164199999997)
    if state.geom is not None:    
        poly = to_shape(state.geom)
        if point.within(poly):
            print('Point is in {0}'.format(state.name))

This should print Point is in Florida but the point is never found in the poly. For those curious, after transforming the geom from the DB to a Shapely poly here are the poly.geoms: pastebin.

3

I don't think it is shapely. Using the data from your pastebin, and a simple test:

>>> import shapely
>>> from shapely.wkt import loads
>>> state = loads('POLYGON ((-80.7856622716253 28.78519435427305, -80.76241527160364 28.73633435422754, -80.83210427166854 28.78618635427397, -80.85070527168587 28.78570035427352, -80.74719027158946 28.39899235391336, -80.49224427135204 27.87017835342087, -80.50935827136797 27.82570835337945, -80.48185327134235 27.84572135339809, -80.40137727126741 27.70358535326572, -80.35737827122642 27.55566235312795, -80.23963827111677 27.26464735285693, -80.19009127107063 27.18568435278338, -80.2239892711022 27.21506635281075, -80.28963527116333 27.24133835283521, -80.32679127119793 27.24826235284166, -80.29042027116407 27.21285335280869, -80.22127327109966 27.20284235279937, -80.14796727103139 27.10906935271203, -80.09036327097775 26.97404335258628, -80.11461027100033 26.97383735258609, -80.050911270941 26.79719735242158, -80.08469527097247 26.32637735198309, -80.12778127101259 25.97753635165821, -80.19309927107342 25.76003235145564, -80.30145627117435 25.61375135131941, -80.32999127120092 25.49001535120417, -80.30197527117483 25.40120035112145, -80.41681627128177 25.24990235098054, -80.4208072712855 25.19221935092682, -80.55187627140756 25.21231835094554, -80.69382527153977 25.15229935088965, -80.85680127169155 25.18563135092069, -80.97587727180245 25.13050135086934, -81.11901627193575 25.13418835087278, -81.14823027196296 25.16469035090119, -81.18378427199607 25.26887935099822, -81.14056927195583 25.32076535104654, -81.01156027183568 25.21442935094751, -80.95102727177931 25.20253735093643, -80.915274271746 25.24672535097759, -80.97428127180096 25.32246035104812, -81.14332327195839 25.39682735111738, -81.25837527206555 25.68110035138213, -81.19994727201113 25.71042235140944, -81.25620727206352 25.80310235149575, -81.5307422723192 25.91465835159965, -81.71829327249388 25.92358135160796, -81.70569527248215 26.00017235167929, -81.79471727256505 26.11116235178266, -81.81385827258288 26.28446735194406, -81.84935127261593 26.33219735198851, -81.86421227262977 26.4395543520885, -81.94201827270224 26.46756235211458, -81.96811927272654 26.51738235216098, -81.9287732726899 26.53498035217737, -81.88427627264846 26.64298735227796, -81.77391427254568 26.71026535234061, -81.8971022726604 26.66376735229731, -81.9444522727045 26.55071935219203, -82.02147327277623 26.52468035216778, -82.08366027283415 26.71579035234576, -82.06827127281981 26.76697035239343, -82.05176727280445 26.86670935248631, -82.09787227284738 26.92178535253761, -81.99144027274826 26.96305635257605, -81.97919427273686 27.03168035263996, -81.99285127274958 27.03167635263995, -82.01268827276805 26.97655035258861, -82.15321027289892 26.93706535255184, -82.25745927299602 27.00440935261456, -82.28217927301904 27.02455735263332, -82.25758227299613 26.9979073526085, -82.1764952729206 26.91369335253007, -82.1538282728995 26.79012835241499, -82.28984027302617 26.84988635247065, -82.35543127308726 26.94889635256285, -82.39817127312706 26.99851535260907, -82.36866827309959 26.94793835256197, -82.34362827307626 26.9036323525207, -82.38003627311018 26.94729635256137, -82.50693027322835 27.23699135283117, -82.53113127325089 27.26027735285285, -82.51448327323538 27.21045335280645, -82.56995727328705 27.27428035286589, -82.53274027325239 27.33180235291947, -82.56556227328295 27.38668135297058, -82.68592527339506 27.47384435305175, -82.63888927335125 27.50345935307934, -82.66582927337633 27.49358735307014, -82.68170627339113 27.52442935309887, -82.48826727321097 27.47806735305569, -82.51419927323512 27.51198935308728, -82.42717727315407 27.5228593530974, -82.57557127329228 27.51232435308759, -82.63876727335114 27.53663835311023, -82.56960527328673 27.55271335312521, -82.55426227327243 27.5822393531527, -82.62637627333959 27.55519435312752, -82.54004127325919 27.60810635317679, -82.55250027327079 27.64401435321024, -82.40437927313285 27.79162835334771, -82.39881727312766 27.90621935345443, -82.46099827318557 27.94015535348604, -82.48313127320618 27.82194735337595, -82.51059827323176 27.8312323533846, -82.53899527325821 27.93572835348192, -82.64556527335746 28.02884735356864, -82.65068427336223 28.00716335354845, -82.69833927340662 28.04616935358477, -82.67271927338275 28.01051435355156, -82.70066627340879 27.97535335351882, -82.6447412733567 27.96658435351065, -82.7254852734319 27.94056235348642, -82.56426427328175 27.87846235342858, -82.62365327333706 27.8485273534007, -82.64461927335658 27.71572535327702, -82.67832627338798 27.70558735326758, -82.79383827349555 27.82965435338313, -82.72816327343439 27.71777335327893, -82.74100327344635 27.68586835324922, -82.84428527354254 27.85064135340267, -82.77914427348188 28.17302735370292, -82.67347627338346 28.42851035394086, -82.63607527334862 28.69274935418695, -82.68138027339081 28.80838235429464, -82.63651927334904 28.81427835430013, -82.63661927334914 28.88470835436572, -82.75532227345968 29.00866035448116, -82.8021572735033 29.15513235461757, -83.03674727372179 29.17938735464016, -83.07392827375641 29.2656523547205, -83.14220627381999 29.29965135475217, -83.10899527378906 29.32818235477874, -83.1746332738502 29.34367935479317, -83.23432827390579 29.43393735487723, -83.38003127404149 29.51987435495727, -83.40497427406471 29.66960235509671, -83.55044827420021 29.73732335515978, -83.65418227429682 29.9109613553215, -83.97173827459255 30.07748335547658, -84.07462527468839 30.0999483554975, -84.14785527475658 30.08172535548053, -84.23307127483595 30.10811135550511, -84.35392527494849 30.06962435546926, -84.36041627495455 29.97739035538336, -84.43769027502651 29.99179135539677, -84.43310427502225 29.95954535536674, -84.33893827493455 29.94714135535519, -84.34691127494197 29.91016835532076, -84.46453827505151 29.92962735533888, -84.85803027541799 29.74686035516866, -84.92851327548362 29.77797235519764, -84.98828327553929 29.71992135514358, -85.21476027575021 29.70155735512648, -85.36487927589002 29.68301935510921, -85.40960427593167 29.77676635519651, -85.41405227593582 29.86306735527689, -85.39299427591621 29.87541335528839, -85.4025462759251 29.79436535521291, -85.35804327588366 29.69125435511688, -85.30658927583573 29.70171035512661, -85.30052427583009 29.80979635522728, -85.38479227590857 29.92380135533345, -85.62849727613553 30.09259135549065, -85.56852827607969 30.09829335549596, -85.47135827598919 30.02186335542478, -85.41501327593672 30.0315523554338, -85.43238627595289 30.04575435544703, -85.38269427590662 30.02427035542702, -85.38265627590658 30.03628635543821, -85.3956722759187 30.05856735545896, -85.46431627598263 30.05116335545207, -85.52971627604354 30.13168635552706, -85.70982927621128 30.17877335557091, -85.66605827617052 30.25168935563882, -85.60017627610917 30.25115135563832, -85.56947527608057 30.31100435569407, -85.71713127621808 30.26506235565128, -85.75270827625121 30.29692935568096, -85.85042027634222 30.28035935566552, -85.82994227632315 30.23281435562124, -85.75791927625608 30.22894535561764, -85.72639327622672 30.12884235552441, -85.98701327646943 30.27443035566, -86.38745927684238 30.38765935576546, -86.50531427695213 30.40997335578624, -86.38810827684298 30.40601235578255, -86.24003327670508 30.39988735577684, -86.24156627670651 30.42852735580352, -86.11464027658829 30.38580235576373, -86.12293427659601 30.42646135580159, -86.21960827668605 30.48785435585877, -86.38787927684277 30.46216735583485, -86.45263927690308 30.50123735587123, -86.61023627704985 30.42365135579897, -86.79034727721759 30.41796335579368, -87.19338227759295 30.35522135573525, -86.93251127734999 30.46356535583615, -87.01440027742626 30.51443435588352, -86.98619327739999 30.5904303559543, -87.01958027743109 30.58746635595154, -87.06927227747737 30.45056435582404, -87.12453327752883 30.56466635593031, -87.17155527757262 30.55773535592385, -87.16010227756196 30.46503335583752, -87.27389727766794 30.35738435573726, -87.4240802778078 30.32367135570586, -87.409256277794 30.40223935577903, -87.34680727773583 30.43149835580628, -87.34739527773638 30.45716935583019, -87.4188162778029 30.48170035585304, -87.44535327782762 30.53138935589931, -87.42206727780592 30.55649335592269, -87.39328127777912 30.62009835598193, -87.39864527778411 30.66801535602656, -87.41889427780298 30.69281035604965, -87.46014027784139 30.70580235606175, -87.52660327790329 30.74849135610151, -87.54190027791753 30.78569535613616, -87.61591527798646 30.84829635619446, -87.62571127799559 30.8769023562211, -87.5898672779622 30.95436135629324, -87.59858027797031 31.00263035633819, -87.16311727756477 31.00315735633868, -86.77936127720736 30.99819135633406, -86.70185227713517 30.99809235633396, -86.38391927683908 30.99153835632786, -86.18147627665054 30.9952253563313, -86.03182227651116 30.99332735632953, -85.48659727600338 31.00099835633667, -85.4850102760019 31.00100135633667, -85.00160727555171 31.00125335633691, -85.00014227555033 30.97931935631648, -84.97356727552558 30.9636643563019, -84.96901227552135 30.92709335626784, -84.93777627549225 30.89495735623791, -84.92692627548215 30.84692235619318, -84.93256427548739 30.80262535615192, -84.92689527548212 30.77608835612721, -84.91534327547136 30.75408935610672, -84.88828927544617 30.74388935609722, -84.86458327542408 30.71450335606986, -84.86300327542261 30.71266435606814, -84.37444827496762 30.69409235605085, -84.2816632748812 30.69041935604742, -84.07556227468925 30.67857735603639, -84.00073027461956 30.67553735603356, -83.73784427437472 30.66049135601955, -83.60905627425478 30.65078435601051, -83.31060027397683 30.63430335599516, -83.30131527396819 30.63328535599421, -83.1324402738109 30.62134135598309, -82.69635627340477 30.59572035595923, -82.58295727329916 30.58905335595302, -82.46313527318756 30.58268935594709, -82.41209227314003 30.57730735594208, -82.22103927296209 30.56707635593255, -82.23839627297826 30.53144435589937, -82.21936727296054 30.50297035587285, -82.19918727294174 30.49001435586078, -82.20556527294768 30.42385335579916, -82.1802342729241 30.36863135574773, -82.16463927290957 30.3612913557409, -82.05276727280538 30.36379435574323, -82.03835427279195 30.3789043557573, -82.04609927279917 30.43406835580868, -82.03512027278894 30.44287835581688, -82.0225092727772 30.47771735584933, -82.00580127276164 30.5709903559362, -82.01365227276895 30.59869035596199, -82.04491127279806 30.65091035601063, -82.03188127278592 30.75753235610993, -82.01296727276831 30.76425635611619, -82.01553827277071 30.79131935614139, -81.98148027273899 30.77835135612931, -81.96155127272043 30.79600735614576, -81.95302927271248 30.82043135616851, -81.9438052727039 30.82424935617206, -81.9075182726701 30.81347535616203, -81.89914127266231 30.82830835617584, -81.87300227263796 30.79908435614863, -81.80319827257296 30.78808535613838, -81.77670827254828 30.7615843561137, -81.75771027253059 30.7696313561212, -81.73667527251099 30.76388835611585, -81.71676927249246 30.7453693560986, -81.64247727242326 30.72908135608343, -81.62798127240977 30.73177435608594, -81.60477127238815 30.71632135607155, -81.60117027238479 30.72488735607952, -81.53659127232466 30.70657735606247, -81.5285952723172 30.72145235607633, -81.49878427228944 30.59860535596192, -81.51811727230745 30.55621235592243, -81.45752827225103 30.45476435582795, -81.48079827227269 30.38054035575882, -81.44326027223774 30.35718735573708, -81.43276727222796 30.24678135563425, -81.30275827210689 29.91305235532344, -81.31649027211967 29.82924035524539, -81.24378427205195 29.73794335516036, -81.23376627204263 29.66902435509618, -81.10988027192725 29.43023935487379, -81.0970542719153 29.35179935480074, -80.7856622716253 28.78519435427305))')
>>> testpoint = loads('POINT(-81.4406293 30.323164199999997)')
>>> if testpoint.within(state):
...    print "inside state"
... 
inside state
0
1

Thanks in a big way to @BradHands for pointing out that my suspicions that it was Shapely were incorrect. I noticed that the geom from Shapely was a POLYGON and in my SQLAlchemy model it was defined as MULTIPOLYGON.

class States(Base):
    __tablename__ = 'states'

    state_id = Column(Integer, primary_key=True)
    abbreviation = Column(String)
    name = Column(String)
    geom = Column(Geography(geometry_type='MULTIPOLYGON', srid=4269))

Once I switched the States.geom definition to geometry_type='POLYGON' my script worked. So my model now looks like this:

class States(Base):
    __tablename__ = 'states'

    state_id = Column(Integer, primary_key=True)
    abbreviation = Column(String)
    name = Column(String)
    geom = Column(Geography(geometry_type='POLYGON', srid=4269))
1
  • So the key outcome is providing all the information in the question... – BradHards Jan 14 '16 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.