-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathPostGIS.sql
104 lines (72 loc) · 18.7 KB
/
PostGIS.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
-- postgis extenstion
CREATE EXTENSION postgis;
--Check Version
SELECT postgis_version();
--Retrieve Locations of specific features
SELECT id, name, ST_AsText(geom)
FROM us_states
WHERE name = 'Texas';
SELECT id, state, ST_AsText(geom)
FROM us_cities
WHERE state = 'TX';
SELECT c.gid, c.county, c.name, s.name, ST_AsText(c.geom)
FROM us_counties c, us_states s
WHERE s.id = 'NY' and s.gid = c.state;
--Query selects the names of US states from a table us_states where the geometry of the state intersects with a polygon defined in the coordinates.
SELECT name FROM us_states WHERE ST_Intersects(geom, ST_GeomFromText(
'POLYGON((-106.65 25.84, -106.65 36.50, -93.51 36.50, -93.51 25.84, -106.65 25.84))', 4326));
--Query gives each counties in us_counties table where the geometry of the county intersects with the MultiPolygon defined by the given coordinates.
SELECT geo_id, name, ST_AsText(geom)
FROM us_counties
WHERE ST_Intersects(geom, ST_GeomFromText('MULTIPOLYGON(((-85.79043399999999 31.320266999999998,-85.79032699999999 31.323452,-85.790116 31.330081999999997,-85.79010000000001 31.336275999999998,-85.790065 31.336907999999998,-85.790064 31.337277,-85.789975 31.341524,-85.79000099999999 31.342857,-85.78986599999999 31.350573999999998,-85.789785 31.356562,-85.789754 31.358887,-85.789594 31.372698,-85.78981499999999 31.383145,-85.78983699999999 31.385468999999997,-85.790294 31.431466999999998,-85.789372 31.48743,-85.78936999999999 31.487561,-85.78930799999999 31.490924999999997,-85.78913999999999 31.540616999999997,-85.789141 31.587865999999998,-85.789142 31.617963999999997,-85.767871 31.617936999999998,-85.765416 31.617933999999998,-85.748251 31.618047999999998,-85.74810000000001 31.61806,-85.74704 31.618060999999997,-85.73048399999999 31.618240999999998,-85.728674 31.618053999999997,-85.704923 31.618066,-85.69631799999999 31.617963,-85.69097 31.617901,-85.636485 31.617746999999998,-85.627912 31.617722999999998,-85.58151099999999 31.617634,-85.563107 31.618019,-85.518729 31.619535,-85.499956 31.620267,-85.416437 31.619466,-85.416022 31.560886,-85.415433 31.536465999999997,-85.415336 31.500168,-85.415774 31.490413,-85.41758 31.450433,-85.41803499999999 31.440769999999997,-85.417735 31.423130999999998,-85.417677 31.419742999999997,-85.41728499999999 31.400534999999998,-85.416558 31.37353,-85.41671 31.363350999999998,-85.4171 31.337287,-85.417536 31.335345999999998,-85.41751 31.329535,-85.417459 31.325734999999998,-85.417372 31.321945999999997,-85.41743199999999 31.315106999999998,-85.417434 31.314973,-85.41753399999999 31.311601,-85.417723 31.300842999999997,-85.417249 31.289738999999997,-85.417142 31.287225,-85.417065 31.286188,-85.41830399999999 31.286271,-85.42622999999999 31.286274,-85.430511 31.286361999999997,-85.435029 31.286454,-85.44741599999999 31.286593999999997,-85.460259 31.286685,-85.465814 31.286815999999998,-85.46723399999999 31.286873,-85.468042 31.286783,-85.468132 31.286772,-85.468215 31.286865,-85.468406 31.286869999999997,-85.476973 31.287069,-85.48544199999999 31.287094999999997,-85.48570000000001 31.274369,-85.485793 31.2589,-85.48580799999999 31.258488,-85.485784 31.257894999999998,-85.485762 31.257348,-85.485793 31.256680999999997,-85.48577999999999 31.256269999999997,-85.485671 31.252747999999997,-85.48574699999999 31.251621,-85.485771 31.250116,-85.48585399999999 31.246095999999998,-85.490386 31.245604,-85.493315 31.246367,-85.494643 31.24634,-85.49683999999999 31.247056999999998,-85.501415 31.24624,-85.503051 31.246305,-85.503789 31.246378999999997,-85.507426 31.249126999999998,-85.507759 31.249381999999997,-85.508882 31.250173,-85.51453599999999 31.251478,-85.51584299999999 31.252257,-85.520163 31.252167,-85.52453799999999 31.253407,-85.526324 31.252145,-85.530565 31.252100000000002,-85.532378 31.251367,-85.534138 31.253064,-85.539525 31.253042999999998,-85.544566 31.253525,-85.54917999999999 31.255633,-85.55086 31.255678999999997,-85.55171299999999 31.257146,-85.5531 31.256871,-85.554007 31.258062,-85.556781 31.258657999999997,-85.559074 31.260354,-85.56016799999999 31.259964,-85.56294199999999 31.261225,-85.564996 31.263195,-85.56784999999999 31.262141,-85.570094 31.262379,-85.57569199999999 31.264523999999998,-85.585534 31.264179,-85.58724199999999 31.265645,-85.58961599999999 31.265895999999998,-85.589483 31.266928,-85.592337 31.267546,-85.594392 31.268988999999998,-85.594659 31.27002,-85.595672 31.26963,-85.59935399999999 31.270248,-85.60295599999999 31.272378,-85.608904 31.271872,-85.60959799999999 31.273222999999998,-85.6144 31.273335999999997,-85.61706799999999 31.272807999999998,-85.619866 31.274344,-85.62168299999999 31.273241,-85.62227 31.274226,-85.624934 31.273730999999998,-85.625757 31.27452,-85.62679899999999 31.273329999999998,-85.629092 31.273811,-85.630747 31.272736,-85.633121 31.273560999999997,-85.636001 31.275578,-85.64018999999999 31.27322,-85.644432 31.272213999999998,-85.646433 31.273222999999998,-85.64984799999999 31.27233,-85.65147499999999 31.27272,-85.652435 31.271803,-85.654943 31.271963999999997,-85.65731799999999 31.270384,-85.65998499999999 31.270888999999997,-85.662893 31.269033999999998,-85.66612099999999 31.267315999999997,-85.666843 31.263077,-85.66867599999999 31.262024,-85.671311 31.258336,-85.671245 31.257441,-85.675113 31.255561999999998,-85.671138 31.250173,-85.679726 31.238076999999997,-85.684179 31.237001,-85.688019 31.236794999999997,-85.689033 31.236221999999998,-85.689139 31.232509999999998,-85.68754 31.228775,-85.690365 31.22582,-85.689298 31.222336,-85.689832 31.221237,-85.693778 31.219998999999998,-85.693938 31.218394999999997,-85.69191099999999 31.217019999999998,-85.691698 31.213445999999998,-85.693938 31.213697999999997,-85.694284 31.212895999999997,-85.692365 31.21191,-85.69335099999999 31.209916999999997,-85.69503 31.210375,-85.695004 31.208060999999997,-85.696923 31.207167,-85.69665599999999 31.204715,-85.69838899999999 31.204165,-85.69948199999999 31.205242,-85.700895 31.204393999999997,-85.700575 31.201529999999998,-85.706199 31.201895999999998,-85.70614499999999 31.197725,-85.706732 31.197129,-85.709344 31.198458,-85.712436 31.196876,-85.710866 31.195179,-85.79140199999999 31.196348999999998,-85.791291 31.209854,-85.79131199999999 31.269325,-85.791361 31.291777,-85.791218 31.293252,-85.791214 31.293550999999997,-85.79124499999999 31.294003999999997,-85.791243 31.294183999999998,-85.791167 31.29534,-85.791089 31.297603,-85.790525 31.317494999999997,-85.79043399999999 31.320266999999998)))', 4326));
--Query gives rows in the us_states table where the geometry of the state intersects with a geometry defined by the given Well-Known Binary (WKB) representation.
SELECT id, name, ST_AsText(geom)
FROM us_states
WHERE ST_Intersects(geom, ST_GeomFromText(ST_AsText('0106000020E61000000100000001030000000100000020000000D7BE805EB8425BC00D33349E088042402CB7B41A12435BC0BA675DA3E5543F402B508BC1C3C45BC0BA675DA3E5543F407C4276DEC60F5CC0996379573DB43F40EF703B342CB45CC0FD321823123F404031B1F9B836AE5CC0CB4A9352D05B4040F4FE3F4E98A15CC072DF6A9DB8604040A14B38F4169E5CC0446E861BF06B4040F4FE3F4E98A15CC03B6D8D08C683404072C3EFA65BAA5CC0E65DF5807984404086A92D7590AE5CC0D55B035B25B44040F4FE3F4E98A15CC07CD5CA845FC640404A253CA1D79F5CC09DBB5D2F4DD94040B63176C24BA25CC0C0B4A84F72F740403656629E959A5CC063D2DF4BE10D414064B0E2546B905CC05D1ABFF04A164140FD169D2CB5885CC0AFB2B6291E2741402387889B53955CC026A8E15B58394140A14B38F4169E5CC09B5434D6FE5A4140C9E9EBF99AA85CC0EB909BE106704140C9E9EBF99AA85CC063B48EAA2680414009FCE1E7BFA45CC0603DEE5BAD9141405DDDB1D826A65CC0583CF54883A9414071AC8BDB68AB5CC0FA2B64AE0CC24140319A95ED43AF5CC033DC80CF0F0D42408E93C2BCC7975CC00AF5F411F81142400FB8AE9811905CC03BC269C18B024240FCFF3861C2895CC0E6B2D1393F034240944F8F6D19835CC0AF5B04C6FA184240944F8F6D19835CC00D33349E08804240B8E864A9F59F5BC0B8239C16BC804240D7BE805EB8425BC00D33349E08804240'), 4326));
--Query Calculates the distance between the two points.
SELECT ST_Distance(
ST_GeomFromText('MULTIPOLYGON(((-85.79043399999999 31.320266999999998,-85.79032699999999 31.323452,-85.790116 31.330081999999997,-85.79010000000001 31.336275999999998,-85.790065 31.336907999999998,-85.790064 31.337277,-85.789975 31.341524,-85.79000099999999 31.342857,-85.78986599999999 31.350573999999998,-85.789785 31.356562,-85.789754 31.358887,-85.789594 31.372698,-85.78981499999999 31.383145,-85.78983699999999 31.385468999999997,-85.790294 31.431466999999998,-85.789372 31.48743,-85.78936999999999 31.487561,-85.78930799999999 31.490924999999997,-85.78913999999999 31.540616999999997,-85.789141 31.587865999999998,-85.789142 31.617963999999997,-85.767871 31.617936999999998,-85.765416 31.617933999999998,-85.748251 31.618047999999998,-85.74810000000001 31.61806,-85.74704 31.618060999999997,-85.73048399999999 31.618240999999998,-85.728674 31.618053999999997,-85.704923 31.618066,-85.69631799999999 31.617963,-85.69097 31.617901,-85.636485 31.617746999999998,-85.627912 31.617722999999998,-85.58151099999999 31.617634,-85.563107 31.618019,-85.518729 31.619535,-85.499956 31.620267,-85.416437 31.619466,-85.416022 31.560886,-85.415433 31.536465999999997,-85.415336 31.500168,-85.415774 31.490413,-85.41758 31.450433,-85.41803499999999 31.440769999999997,-85.417735 31.423130999999998,-85.417677 31.419742999999997,-85.41728499999999 31.400534999999998,-85.416558 31.37353,-85.41671 31.363350999999998,-85.4171 31.337287,-85.417536 31.335345999999998,-85.41751 31.329535,-85.417459 31.325734999999998,-85.417372 31.321945999999997,-85.41743199999999 31.315106999999998,-85.417434 31.314973,-85.41753399999999 31.311601,-85.417723 31.300842999999997,-85.417249 31.289738999999997,-85.417142 31.287225,-85.417065 31.286188,-85.41830399999999 31.286271,-85.42622999999999 31.286274,-85.430511 31.286361999999997,-85.435029 31.286454,-85.44741599999999 31.286593999999997,-85.460259 31.286685,-85.465814 31.286815999999998,-85.46723399999999 31.286873,-85.468042 31.286783,-85.468132 31.286772,-85.468215 31.286865,-85.468406 31.286869999999997,-85.476973 31.287069,-85.48544199999999 31.287094999999997,-85.48570000000001 31.274369,-85.485793 31.2589,-85.48580799999999 31.258488,-85.485784 31.257894999999998,-85.485762 31.257348,-85.485793 31.256680999999997,-85.48577999999999 31.256269999999997,-85.485671 31.252747999999997,-85.48574699999999 31.251621,-85.485771 31.250116,-85.48585399999999 31.246095999999998,-85.490386 31.245604,-85.493315 31.246367,-85.494643 31.24634,-85.49683999999999 31.247056999999998,-85.501415 31.24624,-85.503051 31.246305,-85.503789 31.246378999999997,-85.507426 31.249126999999998,-85.507759 31.249381999999997,-85.508882 31.250173,-85.51453599999999 31.251478,-85.51584299999999 31.252257,-85.520163 31.252167,-85.52453799999999 31.253407,-85.526324 31.252145,-85.530565 31.252100000000002,-85.532378 31.251367,-85.534138 31.253064,-85.539525 31.253042999999998,-85.544566 31.253525,-85.54917999999999 31.255633,-85.55086 31.255678999999997,-85.55171299999999 31.257146,-85.5531 31.256871,-85.554007 31.258062,-85.556781 31.258657999999997,-85.559074 31.260354,-85.56016799999999 31.259964,-85.56294199999999 31.261225,-85.564996 31.263195,-85.56784999999999 31.262141,-85.570094 31.262379,-85.57569199999999 31.264523999999998,-85.585534 31.264179,-85.58724199999999 31.265645,-85.58961599999999 31.265895999999998,-85.589483 31.266928,-85.592337 31.267546,-85.594392 31.268988999999998,-85.594659 31.27002,-85.595672 31.26963,-85.59935399999999 31.270248,-85.60295599999999 31.272378,-85.608904 31.271872,-85.60959799999999 31.273222999999998,-85.6144 31.273335999999997,-85.61706799999999 31.272807999999998,-85.619866 31.274344,-85.62168299999999 31.273241,-85.62227 31.274226,-85.624934 31.273730999999998,-85.625757 31.27452,-85.62679899999999 31.273329999999998,-85.629092 31.273811,-85.630747 31.272736,-85.633121 31.273560999999997,-85.636001 31.275578,-85.64018999999999 31.27322,-85.644432 31.272213999999998,-85.646433 31.273222999999998,-85.64984799999999 31.27233,-85.65147499999999 31.27272,-85.652435 31.271803,-85.654943 31.271963999999997,-85.65731799999999 31.270384,-85.65998499999999 31.270888999999997,-85.662893 31.269033999999998,-85.66612099999999 31.267315999999997,-85.666843 31.263077,-85.66867599999999 31.262024,-85.671311 31.258336,-85.671245 31.257441,-85.675113 31.255561999999998,-85.671138 31.250173,-85.679726 31.238076999999997,-85.684179 31.237001,-85.688019 31.236794999999997,-85.689033 31.236221999999998,-85.689139 31.232509999999998,-85.68754 31.228775,-85.690365 31.22582,-85.689298 31.222336,-85.689832 31.221237,-85.693778 31.219998999999998,-85.693938 31.218394999999997,-85.69191099999999 31.217019999999998,-85.691698 31.213445999999998,-85.693938 31.213697999999997,-85.694284 31.212895999999997,-85.692365 31.21191,-85.69335099999999 31.209916999999997,-85.69503 31.210375,-85.695004 31.208060999999997,-85.696923 31.207167,-85.69665599999999 31.204715,-85.69838899999999 31.204165,-85.69948199999999 31.205242,-85.700895 31.204393999999997,-85.700575 31.201529999999998,-85.706199 31.201895999999998,-85.70614499999999 31.197725,-85.706732 31.197129,-85.709344 31.198458,-85.712436 31.196876,-85.710866 31.195179,-85.79140199999999 31.196348999999998,-85.791291 31.209854,-85.79131199999999 31.269325,-85.791361 31.291777,-85.791218 31.293252,-85.791214 31.293550999999997,-85.79124499999999 31.294003999999997,-85.791243 31.294183999999998,-85.791167 31.29534,-85.791089 31.297603,-85.790525 31.317494999999997,-85.79043399999999 31.320266999999998)))', 4326),
ST_GeomFromText(ST_AsText('0106000020E6100000010000000103000000010000004000000003E962D34A1A57C046EA3D95D3A24440F14927124C1A57C0B854A52DAEA344408FA50F5D501A57C04AB3791C06AB44403526C45C521A57C0BD378600E0AC4440E76ED74B531A57C0897B2C7DE8AE444009C38025571A57C0C804FC1A49B444405BE9B5D9581A57C02BA391CF2BB644408E7406465E1A57C0EB56CF49EFBD444064C746205E1A57C000DF6DDE38C14440EC1681B1BE1657C0C34483143CC14440F92EA52E191657C0FE428F183DC14440663046240A1357C0F6E978CC40C144402FBDFDB9681257C0E44A3D0B42C1444040A374E95F0E57C04E999B6F44C14440CB811E6ADB0C57C058AB764D48C14440E8BB5B59A20B57C0CFDBD8EC48C1444062F54718060A57C0D8EDB3CA4CC1444039419B1C3E0557C0FA27B85851C1444011548D5E0D0557C0AC8A709351C14440CC069964E40457C05930F14751C14440F6CE68AB920457C0D5E940D653C14440F69507E9290457C0E063B0E254C144406C0377A04E0357C08CD5E6FF55C144404B749659840257C0B398D87C5CC14440802A6EDC620257C02C99637957C144402FDB4E5B23FF56C006F357C85CC144408E8F16670CFF56C0ECFA05BB61C14440E15B5837DEFE56C06BB8C83D5DC144400567F0F78BFC56C07BBB253960C144409F53909F8DFC56C033BF9A0304BB444028D53E1D8FFC56C05ED4EE5701BA4440307F85CC95FC56C084622B685AB6444055D7A19A92FC56C0A19E3E027FB4444013807F4A95FC56C08F32E202D0B044402A8E03AF96FC56C027F911BF62AF4440B229577897FC56C07D21E4BCFFAD44402F17F19D98FC56C00FD253E410AD4440041C42959AFC56C04FE449D235AB44409C2FF65E7CFC56C00C569C6A2DA044403A2009FB76FC56C0C556D0B4C49E444046CEC29E76FC56C030B77BB94F9E44407C5D86FF74FC56C0BDE13E726B9C444061342BDB87FC56C0B1C398F4F7944440F2423A3C84FC56C02C2CB81FF0944440368FC360FEFC56C09B374E0AF3944440DAAB8F87BEFD56C04C327216F6944440342A70B20D0457C0A3737E8AE39444405708ABB1840B57C0A626C11BD29444406B7D91D0960E57C08AA9F413CE944440A833F790F01257C077853E58C6944440F6949C137B1457C0300DC347C494444026C5C727641557C002F1BA7EC19444405C52B5DD041A57C05F402FDCB99444405BAFE941411A57C0A6ECF483BA944440253ACB2C421A57C09291B3B0A795444086C43D963E1A57C0AE0B3F389F964440378B170B431A57C0F3B0506B9A9744407E37DDB2431A57C0F677B6476F98444090A2CEDC431A57C059349D9D0C9A4440F3FFAA23471A57C0630B410E4A9A44402A8F6E84451A57C079CC4065FC9F4440ECC039234A1A57C013984EEB36A24440C1DF2F664B1A57C0994528B682A2444003E962D34A1A57C046EA3D95D3A24440'), 4326)
)
FROM us_counties;
--Query calculates the maximum distance between the points.
SELECT ST_MaxDistance(
ST_GeomFromText('POINT(-97.73973129999996 30.513532000000055)', 4326),
ST_GeomFromText('LINESTRING (2 2,2 2)', 4326)
)
FROM us_states;
--Query gives the total area of interest for each county in square kilometers.
SELECT name, SUM(ST_Area(geom::geography)) / 1000000 AS area_sq_km
FROM public.us_counties
GROUP BY name;
--Query returns counties which is less than or equal to 1000 units.
SELECT name, ST_AsText(geom)
FROM us_counties
WHERE ST_DWithin(
geom,
ST_GeomFromText(ST_AsText('0106000020E6100000010000000103000000010000004000000003E962D34A1A57C046EA3D95D3A24440F14927124C1A57C0B854A52DAEA344408FA50F5D501A57C04AB3791C06AB44403526C45C521A57C0BD378600E0AC4440E76ED74B531A57C0897B2C7DE8AE444009C38025571A57C0C804FC1A49B444405BE9B5D9581A57C02BA391CF2BB644408E7406465E1A57C0EB56CF49EFBD444064C746205E1A57C000DF6DDE38C14440EC1681B1BE1657C0C34483143CC14440F92EA52E191657C0FE428F183DC14440663046240A1357C0F6E978CC40C144402FBDFDB9681257C0E44A3D0B42C1444040A374E95F0E57C04E999B6F44C14440CB811E6ADB0C57C058AB764D48C14440E8BB5B59A20B57C0CFDBD8EC48C1444062F54718060A57C0D8EDB3CA4CC1444039419B1C3E0557C0FA27B85851C1444011548D5E0D0557C0AC8A709351C14440CC069964E40457C05930F14751C14440F6CE68AB920457C0D5E940D653C14440F69507E9290457C0E063B0E254C144406C0377A04E0357C08CD5E6FF55C144404B749659840257C0B398D87C5CC14440802A6EDC620257C02C99637957C144402FDB4E5B23FF56C006F357C85CC144408E8F16670CFF56C0ECFA05BB61C14440E15B5837DEFE56C06BB8C83D5DC144400567F0F78BFC56C07BBB253960C144409F53909F8DFC56C033BF9A0304BB444028D53E1D8FFC56C05ED4EE5701BA4440307F85CC95FC56C084622B685AB6444055D7A19A92FC56C0A19E3E027FB4444013807F4A95FC56C08F32E202D0B044402A8E03AF96FC56C027F911BF62AF4440B229577897FC56C07D21E4BCFFAD44402F17F19D98FC56C00FD253E410AD4440041C42959AFC56C04FE449D235AB44409C2FF65E7CFC56C00C569C6A2DA044403A2009FB76FC56C0C556D0B4C49E444046CEC29E76FC56C030B77BB94F9E44407C5D86FF74FC56C0BDE13E726B9C444061342BDB87FC56C0B1C398F4F7944440F2423A3C84FC56C02C2CB81FF0944440368FC360FEFC56C09B374E0AF3944440DAAB8F87BEFD56C04C327216F6944440342A70B20D0457C0A3737E8AE39444405708ABB1840B57C0A626C11BD29444406B7D91D0960E57C08AA9F413CE944440A833F790F01257C077853E58C6944440F6949C137B1457C0300DC347C494444026C5C727641557C002F1BA7EC19444405C52B5DD041A57C05F402FDCB99444405BAFE941411A57C0A6ECF483BA944440253ACB2C421A57C09291B3B0A795444086C43D963E1A57C0AE0B3F389F964440378B170B431A57C0F3B0506B9A9744407E37DDB2431A57C0F677B6476F98444090A2CEDC431A57C059349D9D0C9A4440F3FFAA23471A57C0630B410E4A9A44402A8F6E84451A57C079CC4065FC9F4440ECC039234A1A57C013984EEB36A24440C1DF2F664B1A57C0994528B682A2444003E962D34A1A57C046EA3D95D3A24440'), 4326),
1000
)
LIMIT 10;
--Query returns counties which is less than or equal to 500 units.
SELECT id, name, ST_AsText(geom)
FROM us_states
WHERE ST_DWithin(
geom,
ST_GeomFromText('POINT(-97.73973129999996 30.513532000000055)', 4326),
500
) ORDER BY id
LIMIT 3;
--EXPLAIN/EXPLAIN ANALYZE
EXPLAIN
SELECT id, name, ST_AsText(geom)
FROM us_states WHERE name = 'Texas';
EXPLAIN ANALYZE
SELECT c.gid, c.county, c.name, s.name, ST_AsText(c.geom)
FROM us_counties c, us_states s
WHERE s.id = 'NY' and s.gid = c.state;
-- Create Index
CREATE INDEX IF NOT EXISTS us_counties_name_idx
ON us_counties (name);
CREATE INDEX IF NOT EXISTS us_counties_name_idx
ON us_counties (name);