0

I'm working on a school project involving Spatialite and point in polygon queries. I've successfully followed this example: http://blog.crazystyle.net/?p=255

I am now trying the same sequence of steps with the shapefiles for “Travis and Williamson Counties Karst Zones and Critical Salamander Habitat” downloadable from here and viewable online here. I have manually determined that the GPS point 30.418986, -97.83754 should intersect the polygons Jollyville Plateau salamander subsurf crit. hab., karst zone 3, and Jollyville Plateau salamander surface crit. hab.

However, I cannot get the SQL query I'm running to return the data I expect. Please see the transcript below for the query syntax.

  • Am I running the correct query for this set of data?
  • Does the shapefile have the info I need for this sort of query? I notice the column names for this data are different from the column names in the example census data table.

Please note: I am entirely new to GIS. My project is a software project using GIS data/technology.

Here's a transcript of my spatialite work:

spatialite> spatialite karst.sqlite
SpatiaLite version ..: 4.3.0    Supported Extensions:
    - 'VirtualShape'    [direct Shapefile access]
    - 'VirtualDbf'      [direct DBF access]
    - 'VirtualXL'       [direct XLS access]
    - 'VirtualText'     [direct CSV/TXT access]
    - 'VirtualNetwork'  [Dijkstra shortest path]
    - 'RTree'       [Spatial Index - R*Tree]
    - 'MbrCache'        [Spatial Index - MBR cache]
    - 'VirtualSpatialIndex' [R*Tree metahandler]
    - 'VirtualElementary'   [ElemGeoms metahandler]
    - 'VirtualXPath'    [XML Path Language - XPath]
    - 'VirtualFDO'      [FDO-OGR interoperability]
    - 'VirtualGPKG' [OGC GeoPackage interoperability]
    - 'VirtualBBox'     [BoundingBox tables]
    - 'SpatiaLite'      [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.9.1, 04 March 2015
GEOS version ........: 3.4.2-CAPI-1.8.2 r3921
LWGEOM version ......: 2.1.8
TARGET CPU ..........: x86_64-linux-gnu
the SPATIAL_REF_SYS table already contains some row(s)
SQLite version ......: 3.8.11.1
Enter ".help" for instructions
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
spatialite> .loadshp /mnt/Trav_Will_KarstZones_Veni_07 zones CP1252 26914
========
Loading shapefile at '/mnt/Trav_Will_KarstZones_Veni_07' into SQLite table 'zones'

BEGIN;
CREATE TABLE "zones" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"OBJECTID" INTEGER,
"ZONE_" INTEGER,
"ZONE_DESC" TEXT,
"LABEL" TEXT,
"MAP_ID" DOUBLE,
"Area_km2" DOUBLE,
"Shape_Leng" DOUBLE,
"Shape_Area" DOUBLE);
SELECT AddGeometryColumn('zones', 'Geometry', 26914, 'MULTIPOLYGON', 'XY');
COMMIT;

Inserted 310 rows into 'zones' from SHAPEFILE
spatialite>  select * from zones where within(GeomFromText('POINT(-97.837543 30.418986)'),zones.Geometry);
spatialite> 
  • Your data has SRID=26914, your point has obviously some other SRID but you do not tell it in GeomFromText. – user30184 Aug 23 '15 at 20:32
  • I actually think I have the query working by using UTM instead of GPS lat long: select * from zones where within(GeomFromText('POINT(619705.9237194055 3338561.5768810967)'),zones.Geometry); – fahrradler Aug 23 '15 at 20:40
  • I finally stumbled on the SRID issue just before checking back here. I did try passing in SRID=4326 in the 2nd parameter for GeomFromText, but I did not have any different results with that. So far, I haven't found any doc explaining what I did wrong with that. However, I'm happy UTM is working for now :) – fahrradler Aug 23 '15 at 20:42
  • Did you follow gaia-gis.it/gaia-sins/spatialite-sql-latest.html and the full usage example GeomFromText( wkt String [ , SRID Integer] ) Try with `GeomFromText('POINT(-97.837543 30.418986)',4326). Are your certain about your long-lat coordinates? – user30184 Aug 23 '15 at 20:46
  • Your place it here I guess openstreetmap.org/?mlat=30.418986&mlon=-97.837543#map=15/… – user30184 Aug 23 '15 at 20:56
2

The problem is that your geometries have to match in terms of reference (the Spatial Reference System, which is identified by a number, the SRID).

So if you want to look up a point in degrees (longitude, latitude), but the data is in a UTM zone, you need to use a transformation to get from something referenced to SRID of 4326 to SRID of 26914.

In your case, its going to look like:

SELECT AsEwkt(ST_Transform(GeomFromText('POINT(-97.837543 30.418986)', 4326), 26914));                                                                                                                                                                             
SRID=26914;POINT(611646.0822397435 3365787.373931462) 

So the query will look like:

select * from zones where within(ST_Transform(GeomFromText('POINT(-97.837543 30.418986)', 4326), 26914),zones.Geometry);  
196|196|3|Low Probability E.C.S.|Zone 3|282.0|5.54860404688|31721.2708116|5548598.13761| 

If you actually want to see the geometry, a cleaned-up version would look something like:

spatialite> .headers on
spatialite> SELECT *, AsEwkt(zones.Geometry) FROM zones WHERE within(ST_Transform(GeomFromText('POINT(-97.837543 30.418986)', 4326), 26914), zones.Geometry);                                                                                                                  
PK_UID|OBJECTID|ZONE_|ZONE_DESC|LABEL|MAP_ID|Area_km2|Shape_Leng|Shape_Area|Geometry|AsEwkt(zones.Geometry)                                                                                                                                                                    
196|196|3|Low Probability E.C.S.|Zone 3|282.0|5.54860404688|31721.2708116|5548598.13761||SRID=26914;MULTIPOLYGON(((611573.7601345193 3367032.70061156,611665.5292921809 3367019.177909367,611759.0276835527 3367033.179408131,611773.0800848742 3367026.463827583,611856.8423879268 3366986.430836006,611962.8488944799 3366969.954239872,611965.5487022758 3366969.534578176,612019.2839573367 3366961.180740629,612095.8435360352 3366952.43311295,612138.028156689 3366908.186980588,612138.0309567161 3366908.184980635,612157.9062203565 3366844.186235854,612212.6562202062 3366809.682330023,612286.611921708 3366813.94640981,612289.1350217603 3366814.091843237,612355.9073232616 3366817.938327852,612406.5281924421 3366808.115055825,612406.5465925046 3366808.111456088,612448.6577330529 3366799.939561752,612494.5364123822 3366789.031030713,612525.4092329032 3366781.691480916,612604.7826855006 3366805.191647185,612748.0946462758 3366816.693031209,612945.7204709165 3366818.946190824,613022.5951079293 3366797.697226092,613029.2834695823 3366785.697471065,613078.0969374568 3366698.948525669,613104.9909594336 3366660.416964759,613106.5931905132 3366658.121496877,613133.4106104956 3366619.69973458,613264.1607676955 3366614.951424891,613398.1615110624 3366603.702389103,613585.4739482143 3366433.204305779,613605.3514427035 3366372.706552594,613532.6647273601 3366323.706492361,613482.0411336387 3366297.455126499,613451.2962470744 3366294.866521645,613392.9743726259 3366289.957453625,613322.2902978637 3366330.702709612,613210.6645012043 3366332.451889135,613137.6634977783 3366305.952020621,613068.1028637035 3366266.699556394,613021.7376506193 3366250.067003491,612947.5404504701 3366223.450118454,612868.0413795403 3366209.698402989,612769.6670133559 3366179.447606038,612642.1679347533 3366171.697712227,612518.415901212 3366137.944486142,612433.2300086112 3366072.692324018,612370.7937261497 3366068.489695868,612321.7924399804 3366065.191145904,612210.6677730376 3366034.939773729,612163.064138186 3365997.678994369,612162.02714833 3365996.867183923,612106.5435915403 3365953.438824838,612061.6486411531 3365887.471309621,612061.1920498023 3365886.800506411,612009.7331253946 3365811.18794445,611967.3770416547 3365748.060349959,611965.7133737422 3365745.580738382,611925.359752649 3365685.43755735,611862.6112560016 3365607.686649501,611862.6098560407 3365607.683749493,611850.4861063489 3365565.937069849,611850.808636186 3365541.201683976,611850.8190467972 3365540.40588765,611851.1119464074 3365517.937291309,611851.1150464233 3365517.936291356,611905.5460357181 3365505.686186387,611905.5491356889 3365505.688486418,611936.9866181971 3365538.188169834,612012.3653853783 3365648.752603416,612033.7979787171 3365680.18915486,612175.0471483465 3365838.940666268,612304.5479138891 3365930.190716734,612415.4838675174 3365976.439415346,612504.8983816688 3365979.334016025,612506.1351818664 3365979.374032631,612577.9185933138 3365981.694996564,612631.9810902322 3365991.945684227,612682.448143883 3366005.349208795,612708.2282692533 3366012.194827813,612810.1695279789 3366019.944976367,612854.227303643 3366039.079387768,612886.1670412691 3366052.949658438,612936.9177527978 3366069.699471233,613019.4172143284 3366099.450455667,613146.3156797195 3366108.046138216,613251.9797512188 3366115.202539149,613331.5776869584 3366116.198114498,613392.1663141589 3366116.956033048,613443.4799850448 3366098.456313797,613419.6693352763 3365969.95777768,613334.4195873953 3365908.206301667,613334.4167874304 3365908.203501639,613227.6714030308 3365794.704568103,613087.6734592608 3365776.953245895,612963.9857007812 3365730.451375252,612922.879713534 3365658.995239607,612867.4847457488 3365562.704821175,612808.4250945718 3365449.950926115,612808.4226946192 3365449.947126102,612764.676438432 3365391.700292888,612746.6320023681 3365378.592006146,612710.9893265119 3365352.699637162,612664.8536879298 3365361.715768074,612634.2401958407 3365367.699524137,612611.363600575 3365412.199012548,612518.6133216517 3365430.197568673,612412.6114314555 3365425.791245023,612301.9883418387 3365421.192859281,612120.5501045885 3365402.690671233,612047.9886624516 3365350.694114502,611994.4738936476 3365241.284771749,611954.9298530124 3365160.439891651,611927.1813839663 3365089.690427087,611899.2432593948 3365038.189874245,611871.3093954876 3364989.692907892,611871.3068955319 3364989.689407873,611796.0605198496 3364895.942595434,611796.0577198958 3364895.939195411,611720.6802206232 3364811.687938041,611648.246567587 3364746.688334978,611648.243467618 3364746.685834947,611574.1822090995 3364689.688073638,611466.3704989374 3364643.439402916,611380.370885064 3364590.634358489,611379.6672906853 3364590.202450759,611333.6243588628 3364561.934444813,611232.0626245626 3364525.433115072,611232.0598245742 3364525.432215038,611140.0637220802 3364501.682960006,611050.8751014936 3364494.180172315,610996.6876983073 3364493.430234119,610958.8726139693 3364454.679485187,610972.7239566204 3364437.007051611,611001.1889550399 3364400.680499271,611061.7503584609 3364401.431025127,611079.2845017062 3364391.126410073,611109.8145509425 3364373.182106291,611120.0659577684 3364328.43214172,611169.7140730905 3364313.240088013,611200.1279048105 3364303.933645245,611247.2600046424 3364326.8988906,611254.0038617332 3364330.184668617,611310.255626318 3364407.936899031,611417.8133607751 3364466.685814141,611548.3137536043 3364484.437222369,611681.6852906208 3364521.436584802,611681.6875905829 3364521.439384821,611763.248558556 3364615.186687232,611818.8680247417 3364686.290833773,611948.0583630811 3364851.441468333,611989.5326773942 3364896.481735063,611989.5488771673 3364896.499335207,611989.8501729154 3364896.82653787,612007.6830213748 3364916.191495461,612014.7307448829 3364952.210731809,612031.555707985 3365038.191879638,612132.3682808188 3365125.943964727,612211.4916312141 3365162.1935823,612297.2845539409 3365215.95931214,612300.05473211 3365217.695242154,612375.9906413231 3365257.195500701,612375.994441321 3365257.195700751,612474.6780834458 3365264.696812199,612517.8488047108 3365256.858135754,612519.3687089781 3365256.582057708,612573.8009616793 3365246.694144032,612593.347116009 3365182.369799234,612603.8049729272 3365147.950296356,612598.3679555743 3365080.699324005,612570.6189370353 3365012.949149204,612552.4323193789 3364945.449403485,612543.809302162 3364878.199687019,612544.4963774038 3364826.949725958,612579.8844641092 3364798.414437386,612582.0537877993 3364796.665174874,612599.3715768593 3364782.700374149,612640.4306868781 3364805.69853219,612640.4341868459 3364805.701132225,612690.8709619368 3364854.451702622,612726.3560260386 3364880.899768604,612727.4902153088 3364881.7450803,612747.5580255514 3364896.702187204,612747.5617255545 3364896.701987257,612753.4190264728 3364896.77876692,612753.4547264768 3364896.779267404,612804.9951345648 3364897.453068411,612825.1833862947 3364817.70440315,612817.4519795282 3364787.520733303,612807.061508181 3364746.956773747,612807.0603082249 3364746.953473745,612775.9983452213 3364682.453638994,612739.1245164175 3364592.453338846,612721.7032972829 3364547.87890052,612721.5855012116 3364547.57750026,612711.3721418593 3364521.450677696,612712.1263399255 3364460.704660531,612738.1869446786 3364422.708087904,612738.1892447319 3364422.704087952,612836.5020876339 3364459.206670482,612870.8763634198 3364507.455524181,612882.74963982 3364578.204768341,612926.6841738623 3364629.953736489,612926.6865738336 3364629.955936511,612998.8753024794 3364590.95670009,613031.2500677572 3364555.958801068,613094.1869016963 3364608.208326507,613086.8110594882 3364687.954165274,613086.8107594421 3364687.957765252,613024.9339800643 3364776.955517122,612991.9104311325 3364841.173075835,612959.4961969122 3364904.206248483,612966.4928713908 3365089.95440547,612988.5113177525 3365163.578173252,612988.5652154231 3365163.758273174,613003.0537883504 3365212.204351784,613030.9093919078 3365251.091655717,613031.5250809398 3365251.951060223,613056.2412409111 3365286.454141017,613087.6052631003 3365324.103298002,613134.5516489762 3365380.455182296,613155.3557503222 3365434.80721916,613156.2953187802 3365437.262020818,613177.6144035207 3365492.952758382,613222.3236257178 3365538.979957977,613223.0036169374 3365539.679964052,613249.9864687151 3365567.45690505,613336.1749627809 3365555.707831555,613421.4207844496 3365611.206138754,613421.4233844194 3365611.208538778,613481.0478494206 3365685.708709918,613481.0499493929 3365685.710909938,613547.1086267398 3365744.20984096,613601.1095386086 3365760.959798553,613625.7911307415 3365731.587768625,613646.4851592606 3365706.960762834,613669.5489257078 3365656.210909023,613720.6110672288 3365647.211944969,613742.2342145766 3365698.711802937,613753.6710808335 3365794.96171679,613838.7958903667 3365859.962675184,613858.0744453393 3365879.847045712,613858.5971386971 3365880.386150335,613876.6097099614 3365898.962409615,613962.6707866357 3365896.714489268,613998.0448267561 3365871.714985169,614025.4805668863 3365846.345875015,614056.1725469927 3365817.96612312,614107.2340679322 3365818.467014932,614158.1716856248 3365819.216203923,614190.2279106067 3365826.431406336,614202.6077816603 3365829.217861735,614305.2957826835 3365896.218947154,614349.9832200802 3365895.220359042,614394.6079574166 3365894.2209701,614484.2321768142 3365872.96878686,614564.1732931646 3365851.469773467,614600.7942555627 3365840.220123674,614653.8561930065 3365823.72262194,614687.4843659729 3365811.473436283,614704.4858549385 3365805.154496936,614721.111041882 3365798.975751834,614781.8579711917 3365791.272313888,614880.7974812661 3365778.726018122,614969.91384379 3365777.442236213,615002.048366309 3365776.979375458,615028.5486240978 3365693.979725081,614985.0680445511 3365650.913935132,614950.0484643663 3365616.229020832,614867.9257124338 3365561.730357553,614842.9246778285 3365595.227560608,614791.9246113194 3365606.476813734,614791.9211113029 3365606.47791368,614723.8617907791 3365629.225881064,614668.1050603942 3365637.37858414,614607.6115186599 3365646.224119607,614511.2368253157 3365626.222900917,614451.6734827585 3365674.473266218,614369.4240911727 3365623.471283506,614329.7366985395 3365629.220516711,614247.4868711198 3365651.717993239,614187.9867463407 3365612.218666121,614142.6114242768 3365612.217548549,614100.1106333183 3365680.21725631,614029.1725797939 3365697.215412864,613975.2991541441 3365634.715367848,613961.1136549423 3365563.965900469,613952.5544457347 3365517.61339723,613946.9883300567 3365487.468960106,613946.9881300955 3365487.465960117,613946.9888687628 3365390.965703939,613924.3022863315 3365365.465812,613918.7866165308 3365347.357220045,613904.4282160296 3365300.216240896,613886.855836399 3365274.719818394,613873.2412847566 3365254.966123428,613873.2402847154 3365254.9691234,613851.9907849607 3365308.715386983,613830.7399846435 3365362.465050663,613776.8646401689 3365410.714995215,613754.1759086328 3365458.963564931,613717.2998413174 3365532.712724742,613652.1116138654 3365501.462180287,613569.8647288421 3365427.71159741,613557.7135278384 3365419.633068791,613501.8019839606 3365382.460376773,613419.6149377173 3365336.959264288,613351.554186305 3365246.209849633,613368.5544024702 3365183.834365239,613385.5553181557 3365121.459980949,613390.3417518865 3365103.577827623,613396.930873583 3365078.960829551,613370.3147683155 3365086.321832803,613314.680547949 3365101.709103607,613249.4922278267 3365124.459010772,613212.6181377258 3365053.457229684,613172.9316446352 3364982.707708558,613206.9328055622 3364917.458368606,613172.9335440903 3364852.207599682,613172.9336761719 3364826.708515149,613181.6560746954 3364803.989937241,613201.2473449194 3364752.959036174,613222.9222765255 3364735.64541098,613243.8109995535 3364718.960372242,613243.8136995738 3364718.958872283,613269.0149403502 3364708.68006354,613313.6853895785 3364690.459757166,613254.8742056919 3364626.460442728,613254.876105739 3364626.45694277,613275.3123448425 3364577.710543335,613305.9966487732 3364547.214801584,613306.0002488171 3364547.21140165,613311.9061083216 3364519.653007447,613321.376184654 3364475.462037472,613408.3154970367 3364391.213410634,613423.6912994524 3364352.714596103,613398.066425851 3364319.464395762,613344.3782157644 3364319.463159807,613344.3749157668 3364319.462959763,613295.8153716188 3364314.46201677,613198.6293886823 3364288.711600977,613166.4921510028 3364291.153649356,613129.5660076533 3364293.959930458,613088.630268064 3364181.460076798,613037.5048938749 3364171.209321684,612945.3810841174 3364109.7082346,612945.3780841445 3364109.706034568,612822.6347057718 3364015.209074312,612822.6321057979 3364015.207074285,612808.0522574 3364003.51472656,612745.9452041122 3363953.706396969,612699.8843430527 3363851.456121966,612595.0096441771 3363789.955155481,612513.198380334 3363733.703881699,612444.1360801259 3363710.703334505,612416.8846198453 3363684.882074649,612392.9492185011 3363662.202846317,612357.0231938423 3363671.517910761,612334.1364143409 3363677.452069613,612260.0122920594 3363610.95124517,612260.0119921077 3363610.947445183,612259.4698827412 3363604.105468072,612252.3260771832 3363513.951669535,612254.8890177988 3363480.701752223,612224.2012797493 3363506.201316715,612180.7043155201 3363495.951463304,612180.7014155287 3363495.950763266,612183.2640618694 3363439.701147572,612150.0137261418 3363449.949844325,612136.7925802757 3363438.350313573,612065.9528073567 3363376.199712541,612002.891747674 3363320.699988636,611886.454752532 3363230.697880236,611775.330293794 3363183.195556819,611741.0822262043 3363188.421861622,611706.5171578922 3363193.69716187,611730.327877219 3363251.941634641,611730.3295771654 3363251.945634647,611772.7042873591 3363280.945791394,611844.1409364547 3363344.446496673,611894.3905915411 3363355.197241657,611934.0774873881 3363407.94755599,611968.4523737219 3363423.947958878,611996.2328576716 3363470.198337401,612024.0132422433 3363516.448515689,612039.8867754526 3363611.698613411,612026.6989777291 3363701.699134352,611973.7600465241 3363786.446332245,611944.6333886984 3363865.6958821,611942.0080059492 3363931.944453511,611942.0069901463 3364000.695249843,611998.5966752311 3364017.370152884,612031.9446487878 3364027.196167119,612084.8810245038 3364074.69678326,612132.5053214052 3364135.69706625,612121.94149171 3364228.197511156,612076.9404275638 3364307.695741855,611973.7521622756 3364371.19404689,611886.4394273017 3364394.942945994,611809.6893045564 3364357.94135821,611803.0657359603 3364347.99491137,611790.3775877612 3364328.941721607,611767.3780439973 3364294.441358628,611711.8168559025 3364225.441001164,611640.3799934125 3364161.940401157,611606.8957639156 3364148.805099727,611553.0679384287 3364127.689354266,611521.3186042354 3364040.439201497,611473.6935862416 3364056.188879021,611444.5687527178 3364050.938102432,611431.3819145844 3364016.437072548,611407.5690887657 3364040.437340752,611381.068504945 3364098.437123429,611367.880301794 3364135.686479604,611344.0678349961 3364132.937465083,611307.0051153501 3364111.684849704,611235.5669761708 3364143.433831415,611201.1921043354 3364140.933270984,611185.3180594281 3364114.434368859,611164.3469703203 3364105.979618113,611145.6307693574 3364098.433894305,611103.2549177334 3364072.183027397,611029.193665555 3364045.682526715,611029.1929226728 3364093.181920235,611031.8179272118 3364122.432229063,610997.4418423408 3364135.681500112,610933.9424608794 3364140.930806677,610870.4415367473 3364156.679267771,610806.9430861193 3364130.17841204,610806.9403861414 3364130.176712011,610761.9431445108 3364095.928643242,610735.5053442424 3364125.053154747,610709.0670436274 3364154.176666357,610730.1912228353 3364244.177566636,610695.8144805828 3364312.92589834,610645.5647897338 3364326.175752876,610600.5638655871 3364341.925668604,610571.4400218717 3364344.674957941,610555.5648660764 3364363.17416075,610603.1866152376 3364389.673998321,610603.1892152202 3364389.675198352,610603.1889630338 3364408.175118447,610579.3759621053 3364437.174667287,610603.1855302408 3364439.924281262,610603.18853023 3364439.924981299,610656.1264036304 3364413.425820364,610709.0629817791 3364415.924934146,610693.1881478885 3364476.92595291,610653.4997589041 3364556.17406711,610695.8112754883 3364548.425579338,610772.5623270097 3364516.676866729,610833.3739935446 3364498.178378927,610875.7497903733 3364513.929590323,610887.6557780555 3364529.80388426,610899.5617658067 3364545.679678158,610902.1861072133 3364572.174798931,610902.1868071603 3364572.178598923,610957.7483641252 3364561.679504489,611026.5618773037 3364569.429911956,611042.4355269055 3364662.179624941,611092.6843331011 3364707.180115551,611166.7454642168 3364773.181539385,611222.3056669308 3364826.178766626,611222.3085668935 3364826.181466654,611261.9954811988 3364863.181946709,611277.8686433134 3364910.932554302,611272.6199664324 3364923.930725714,611243.4938036722 3364942.681446025,611218.9628367549 3364939.590424728,611179.9937893829 3364934.679514313,611155.0545971414 3364940.86914685,611137.6812328419 3364945.180690885,611076.8052613639 3365000.678317732,611023.8667381354 3365037.928532951,610984.1804473202 3365050.927534938,610968.3032070817 3365119.926917592,610941.8651871171 3365164.926661021,610899.5535276758 3365175.42573858,610806.927676096 3365191.174307704,610792.7970516493 3365199.909877163,610772.5533732491 3365212.425146915,610777.8028581186 3365228.423948901,610817.4886897394 3365249.424898249,610894.2387462321 3365291.925758666,610973.6134253821 3365318.175725223,611066.2390208402 3365328.927139655,611119.1142231052 3365315.67821768,611158.8017405075 3365315.678658194,611198.4863747584 3365336.677706672,611198.4889747425 3365336.679206701,611222.3012988837 3365357.929437822,611244.4347849278 3365367.123698877,611271.0510480182 3365378.179912765,611298.0267112005 3365411.540433572,611316.7379084592 3365434.68028664,611331.1193486498 3365469.361029994,611347.4242276316 3365508.680479026,611434.360417456 3365639.181487064,611480.3575354217 3365833.430855655,611477.8575052098 3365902.67751614,611477.8566051695 3365902.680416115,611447.1688493441 3365920.429921244,611447.1657493399 3365920.430021201,611392.6063371651 3365926.429554217,611351.7921692845 3365922.277118556,611309.0438029916 3365917.928357485,611242.5446069055 3365899.927234028,611246.5080374334 3365964.659603287,611250.2296213612 3366025.427086594,611326.9163726395 3366099.427500889,611367.8537603478 3366109.678110763,611367.8543603093 3366109.681010759,611378.103353618 3366140.429414139,611378.1028021271 3366188.924900272,611378.1029020895 3366188.927800259,611401.102708021 3366211.927010473,611457.7273373393 3366219.92894241,611485.4767793572 3366255.429061516,611481.2309555123 3366271.914831136,611474.7887158109 3366296.928633322,611531.5379441084 3366342.429000614,611572.4754974005 3366362.929764151,611631.2873445911 3366437.180031212,611634.7035060124 3366462.679964356,611636.4115367352 3366475.430630915,611608.2853272584 3366541.930755501,611577.5973113493 3366616.178711329,611582.6586583295 3366687.677462711,611570.5959970825 3366728.928416783,611547.1946904824 3366735.547471282,611526.4073957232 3366741.427164397,611470.1567523397 3366741.426104509,611426.6580233799 3366726.176084348,611426.6552234222 3366726.172684326,611400.8459263979 3366694.675874891,611395.7616044373 3366688.574633161,611390.8460799034 3366682.675792809,611375.5295116043 3366686.896167186,611362.7209544659 3366690.425478516,611362.7200544293 3366690.428178493,611332.0326037741 3366759.424859156,611293.6568553349 3366805.423937794,611279.9235826607 3366824.890366445,611262.9677458545 3366848.922931478,611250.218012311 3366902.673022219,611239.9670752764 3366971.672579442,611258.153093499 3367082.921933585,611499.2789249246 3367043.675758411,611573.7601345193 3367032.70061156)))

If you want those coordinates in degrees, you can use the ST_Tranform function in the query to turn them back into 4326.

spatialite> SELECT *, AsEwkt(ST_Transform(zones.Geometry, 4326)) FROM zones WHERE within(ST_Transform(GeomFromText('POINT(-97.837543 30.418986)', 4326), 26914), zones.Geometry);   
PK_UID|OBJECTID|ZONE_|ZONE_DESC|LABEL|MAP_ID|Area_km2|Shape_Leng|Shape_Area|Geometry|AsEwkt(ST_Transform(zones.Geometry, 4326))
196|196|3|Low Probability E.C.S.|Zone 3|282.0|5.54860404688|31721.2708116|5548598.13761||SRID=4326;MULTIPOLYGON(((-97.8381627423 30.4302282430562,-97.8372087465067 30.4300977309588,-97.83623380139934 30.4302153788985....)))
|improve this answer|||||
1

My issue relates to differing coordinate systems. My source data (the shapefiles) is using SRID 26914. Most GPS coordinates are using SRID 4326.

I used this service to convert my GPS points into SRID points and with this query:

select * from zones where within(GeomFromText('POINT(611646.08 3365787.37)'),zones.Geometry);

I got the results I expected.

I am hopeful that I can find a more automatic way to convert my GPS coordinates to the target SRID in the near future. I'll post back when I figure that out!

|improve this answer|||||
  • Somehow select * from zones where within(GeomFromText('POINT(-97.837543 30.418986)', 4326),zones.Geometry); does not work although I expect from documentation and @user30184 that it should! – fahrradler Aug 23 '15 at 21:38

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.