PostGis - Cadastrando áreas e buscando a área que atende a coordenada

postgis_example.sql

-- http://www.gmapas.com/poligonos-ibge/municipios-do-brasil
-- Download do arquivo KML com 5.566 municípios brasileiros
-- https://docs.google.com/a/gmapas.com/file/d/0B2yOq3AMumqRM2Q0cXVETVJvb1U/edit
CREATE EXTENSION postgis;

CREATE TABLE cidades (
    id SERIAL PRIMARY KEY,
    cidade VARCHAR(64),
    estado VARCHAR(2),
    polygon GEOMETRY
);

CREATE INDEX areas_polygon_idx ON areas USING GIST (polygon);

DELETE FROM cidades;

INSERT INTO cidades (id, cidade, estado, polygon) VALUES (
  3156700, 'Sabará', 'MG',
  ST_GeomFromKML(
      '<Polygon>
			<outerBoundaryIs>
				<LinearRing>
					<coordinates>
						-43.72817990647962,-19.73529303712878,0 -43.72363550344114,-19.74333050317857,0 -43.71012648394364,-19.7410520283087,0 -43.70925974309861,-19.74782251789379,0 -43.70620916889616,-19.75402390092809,0 -43.69732217881407,-19.75486655513845,0 -43.68898877024361,-19.76750629994293,0 -43.6884910319524,-19.78711437591495,0 -43.68833249752964,-19.78742027052787,0 -43.69176545243023,-19.80021068997715,0 -43.68171320669943,-19.80863416622535,0 -43.68043261449731,-19.81344812330876,0 -43.67517799967618,-19.81412258490037,0 -43.6737875565152,-19.82061441470443,0 -43.67674341202122,-19.82316501160102,0 -43.69408533118785,-19.82459993579701,0 -43.70106665002159,-19.82208469792022,0 -43.7188485535517,-19.82937281804568,0 -43.71369269727114,-19.83997406197723,0 -43.71494451191954,-19.85017252384247,0 -43.72343497817746,-19.86109361025576,0 -43.71195931680739,-19.87500517362671,0 -43.70801666543639,-19.88732356990798,0 -43.71734839992022,-19.88842139794424,0 -43.72363185497139,-19.87833967372822,0 -43.72823436841374,-19.88018809047864,0 -43.73468377694316,-19.87753222131205,0 -43.73900914192496,-19.88027472778576,0 -43.73748902141455,-19.88913749043086,0 -43.7403818297302,-19.89291836188877,0 -43.74906325123325,-19.89779846907995,0 -43.74765417411337,-19.90136773217512,0 -43.74709881861134,-19.90754723535954,0 -43.73512757472977,-19.91994078591408,0 -43.73002447933113,-19.94288828570988,0 -43.71988152649102,-19.95715307316768,0 -43.72320648306954,-19.96741905466445,0 -43.73381909388792,-19.96109169673938,0 -43.73656611760845,-19.95477962902744,0 -43.74759878426212,-19.95875043064189,0 -43.75995781527553,-19.95781544464427,0 -43.77496387217074,-19.95098626290613,0 -43.77826886724107,-19.94430304373859,0 -43.79229616482413,-19.95078204739825,0 -43.81190473405837,-19.9436309122102,0 -43.82424195159616,-19.94150988924638,0 -43.82666747198252,-19.94471081375135,0 -43.8286503878703,-19.94022949140146,0 -43.82762344502438,-19.93493545162788,0 -43.83376829098324,-19.94042233436988,0 -43.83512479355618,-19.92785992090126,0 -43.84726495708101,-19.92786045484835,0 -43.84856687158495,-19.9312628598575,0 -43.85270999833607,-19.92816745755887,0 -43.85932450676448,-19.93199063930102,0 -43.87107243063908,-19.92830729299281,0 -43.8694090696284,-19.92475958899931,0 -43.88210531170386,-19.90588729587264,0 -43.88173832507256,-19.90165026243779,0 -43.88691723489488,-19.89244856377792,0 -43.89032031568832,-19.89235560194729,0 -43.8916306180124,-19.88533224162043,0 -43.90596253138031,-19.88603689804799,0 -43.90953595765173,-19.87623434372246,0 -43.89932677862067,-19.87500605234407,0 -43.89660023714897,-19.86407281779988,0 -43.8825387650818,-19.86182048714568,0 -43.87718241645603,-19.86749036729696,0 -43.86293766900003,-19.85591828183929,0 -43.85730165368344,-19.85780445859561,0 -43.86672915774015,-19.8476700925613,0 -43.8679260266608,-19.83996523646423,0 -43.86349384604169,-19.83755589763371,0 -43.86598451388276,-19.83135701377303,0 -43.86224050208291,-19.82738965029927,0 -43.85520799185758,-19.83418946186389,0 -43.85040566546175,-19.83260522818702,0 -43.84570422391771,-19.82961469485761,0 -43.83773720469256,-19.82556777685044,0 -43.83026146801313,-19.82861097567337,0 -43.82637548885712,-19.83461073751065,0 -43.81779802163472,-19.83284535355488,0 -43.80815429882541,-19.84217879537076,0 -43.80560881094765,-19.83852457980162,0 -43.79243090181022,-19.83574264032328,0 -43.78065413586512,-19.84892523096904,0 -43.76710386674827,-19.84142940175239,0 -43.78082177952126,-19.83707389051076,0 -43.7854596198134,-19.82428922016411,0 -43.78064883296378,-19.82120571820314,0 -43.77541141403338,-19.80928916067077,0 -43.79310106296317,-19.80519253739398,0 -43.79862610031503,-19.79896664307533,0 -43.79937774750917,-19.79176127287142,0 -43.80426645348279,-19.7863611450896,0 -43.80416641894413,-19.77218388124958,0 -43.8043797524706,-19.76239061011159,0 -43.79656835737462,-19.75457864593519,0 -43.78006842793938,-19.75454914389929,0 -43.76849351578919,-19.74450979514803,0 -43.76615806393425,-19.73849086871555,0 -43.77182108023945,-19.73646871553613,0 -43.7689648360662,-19.73035714420942,0 -43.77104110877094,-19.72627861137688,0 -43.76418658338992,-19.72515538702124,0 -43.75743248337452,-19.73102127290437,0 -43.72817990647962,-19.73529303712878,0
					</coordinates>
				</LinearRing>
			</outerBoundaryIs>
		</Polygon>'
  )
);


INSERT INTO cidades (id, cidade, estado, polygon) VALUES (
  3106200, 'Belo Horizonte', 'MG',
  ST_GeomFromKML(
      '<Polygon>
			<outerBoundaryIs>
				<LinearRing>
					<coordinates>
						-43.94618915100504,-19.77726616592848,0 -43.94774102478227,-19.79274992634589,0 -43.94359766778729,-19.79808284878709,0 -43.93390622740421,-19.7956257719255,0 -43.92073858147548,-19.80318448438677,0 -43.9141509063148,-19.79761354736711,0 -43.90259896881773,-19.80486964044026,0 -43.89917021088257,-19.80241202952133,0 -43.89876848452775,-19.80901157742083,0 -43.88974498905444,-19.8158898185117,0 -43.87707907038417,-19.81384345797773,0 -43.87621674756611,-19.81898433153322,0 -43.86976749291088,-19.8199485609495,0 -43.86876199553979,-19.82546773986263,0 -43.86224050208291,-19.82738965029927,0 -43.86598451388276,-19.83135701377303,0 -43.86349384604169,-19.83755589763371,0 -43.8679260266608,-19.83996523646423,0 -43.86672915774015,-19.8476700925613,0 -43.85730165368344,-19.85780445859561,0 -43.86293766900003,-19.85591828183929,0 -43.87718241645603,-19.86749036729696,0 -43.8825387650818,-19.86182048714568,0 -43.89660023714897,-19.86407281779988,0 -43.89932677862067,-19.87500605234407,0 -43.90953595765173,-19.87623434372246,0 -43.90596253138031,-19.88603689804799,0 -43.8916306180124,-19.88533224162043,0 -43.89032031568832,-19.89235560194729,0 -43.88691723489488,-19.89244856377792,0 -43.88173832507256,-19.90165026243779,0 -43.88210531170386,-19.90588729587264,0 -43.8694090696284,-19.92475958899931,0 -43.87107243063908,-19.92830729299281,0 -43.87570147520921,-19.93250651514322,0 -43.89194348615903,-19.94723934796794,0 -43.91000055185968,-19.96015464659112,0 -43.91647181924433,-19.96406780998937,0 -43.9398000568919,-19.97492701522373,0 -43.96575177816254,-20.00479928471587,0 -43.97537594678286,-20.00891290991963,0 -43.99253958982834,-20.02928467075043,0 -44.00220485203217,-20.05739221346894,0 -44.01262382113648,-20.05977838317127,0 -44.01751608767486,-20.05386287747265,0 -44.01029388779039,-20.04204933856868,0 -44.0231277160748,-20.03098594623222,0 -44.03632594312077,-20.02025377704805,0 -44.04866039255819,-19.99837752498698,0 -44.05420073635116,-19.99599792780922,0 -44.05653918878104,-19.98151734154269,0 -44.06237223408647,-19.97478784707399,0 -44.04565317264626,-19.97367258535888,0 -44.03448331548435,-19.98326508560654,0 -44.0307395365372,-19.97650349102502,0 -44.01280134699265,-19.96815960364287,0 -44.01396477220229,-19.95610663319723,0 -44.00577032809795,-19.95500031248914,0 -44.00899501978169,-19.95327606768818,0 -44.00884438838672,-19.9510088384316,0 -44.01051491291594,-19.94883119851581,0 -44.02407729516757,-19.94338333861224,0 -44.0271262741008,-19.93741936833965,0 -44.02238336395496,-19.92500818847487,0 -44.02678657279775,-19.91812187447133,0 -44.02926208179135,-19.91542683841199,0 -44.02498684176135,-19.90868022161018,0 -44.02671329596105,-19.89889102769128,0 -44.01537833393107,-19.89046881190169,0 -44.01332881624381,-19.88106273976743,0 -44.02074099270659,-19.86747879107425,0 -44.01876928067869,-19.86008510012612,0 -44.01379244544157,-19.86048742308508,0 -44.01327139411772,-19.85476508131443,0 -44.00913234828209,-19.85415659481979,0 -44.02021648777885,-19.84007463012207,0 -44.01723708393614,-19.83165851163259,0 -44.01316220622704,-19.83004232493818,0 -44.01398692959074,-19.82629254901083,0 -44.01015838458913,-19.82601757264956,0 -44.00574371112315,-19.82138093769439,0 -44.00795976869131,-19.81349324486248,0 -44.00231804968423,-19.80232066331954,0 -43.99229521351332,-19.79570911104209,0 -43.99051419360002,-19.78586436699662,0 -43.97618449852621,-19.78166760706295,0 -43.96565527379714,-19.79168981594669,0 -43.96618065543858,-19.78532090749021,0 -43.95951274343744,-19.77847836460176,0 -43.94872555804248,-19.77724625441677,0 -43.94618915100504,-19.77726616592848,0
					</coordinates>
				</LinearRing>
			</outerBoundaryIs>
		</Polygon>'
  )
);

select * from cidades;

SELECT cidade, estado
FROM cidades
WHERE ST_Contains(
  polygon,
  -- Praça Sete em Belo Horizonte
  ST_GeomFromKML('<Point><coordinates>-43.9409542000000,-19.9190028000000</coordinates></Point>')
);