In [2]:

    

import sys
import os
import psycopg2
import pandas as pd
from IPython.display import display
sys.path.insert(0, os.path.realpath('..'))
import django
django.setup()

conn = psycopg2.connect("service=firecares")

# Handles the cases where geoms appear to be 3857 instead of 4326 (lat/lon)


    

In [5]:

    

q = """
select id, state, ST_X(geom), ST_Y(geom), ST_X(ST_Transform(ST_SetSRID(geom, 3857), 4326)), ST_Y(ST_Transform(ST_SetSRID(geom, 3857), 4326))
from firestation_usgsstructuredata
where ST_X(geom) > 180 or ST_Y(geom) > 180 or ST_X(geom) < -180 or ST_Y(geom) < -180;
"""

pd.read_sql_query(q, conn)


    

    
Out[5]:






  

    

      

      
id
      
state
      
st_x
      
st_y
      
st_x
      
st_y
    
  
  

  

In [4]:

    

q = """
update firestation_usgsstructuredata
set geom = ST_Transform(ST_SetSRID(geom, 3857), 4326)
where ST_X(geom) > 180 or ST_Y(geom) > 180 or ST_X(geom) < -180 or ST_Y(geom) < -180;
"""

c = conn.cursor()
c.execute(q)


    

In [28]:

    

df = pd.read_csv("../stations.csv")


    

In [42]:

    

from firecares.firestation.models import FireStation

for s in df[['X', 'Y', 'StationNum']].to_dict(orient='records'):
    fs = FireStation.objects.filter(department_id=77286, station_number=s.get('StationNum')).first()
    if not fs and s.get('StationNum') == 27:
        fs = FireStation.objects.get(id=1521)
        
    print '{} vs {},{}'.format(fs.geom.coords, s.get('X'), s.get('Y')) 
    fs.geom.x = s.get('X')
    fs.geom.y = s.get('Y')
    fs.save()


    

    




(-9383067.4697, 4060599.1174999997) vs -84.2893848662,34.2365294033
(-9390790.9553, 4062443.507100001) vs -84.3589104511,34.2501706846
(-9406400.3388, 4052058.2250000015) vs -84.4991319293,34.1730208322
(-9405741.5571, 4064187.5003999993) vs -84.4932139923,34.2631194789
(-9412587.7301, 4071276.2192) vs -84.5545718401,34.3156841291
(-9418820.7172, 4058302.6202000007) vs -84.6107060862,34.2194174634
(-9381786.893, 4077873.1225000024) vs -84.2777984389,34.3649880544
(-9416619.9789, 4072767.869400002) vs -84.5906056072,34.3265697503
(-9420685.3844, 4081947.3716999963) vs -84.6274566766,34.3948693327
(-9397934.8774, 4067876.3337000012) vs -84.4230853956,34.290501745
(-9411482.4055, 4053414.3778000027) vs -84.5447849108,34.1830993863
(-9397167.4376, 4051148.590499997) vs -84.4161913666,34.1662600055
(-9393201.7564, 4052114.4641999975) vs -84.3805670456,34.1734388108
(-9415903.658, 4047491.0276999995) vs -84.5844337441,34.1391963196
(-9420215.0105, 4041671.8396999985) vs -84.6232312363,34.0957921813
(-9405721.6483, 4054997.6328999996) vs -84.4930351488,34.194864178
(-9381025.5182, 4057376.714400001) vs -84.2711860372,34.2125394807
(-9418530.9295, 4069826.3649000004) vs -84.6079347593,34.3051914537
(-9392090.7241, 4080036.833800003) vs -84.3706010528,34.3808498388
(-9407725.6633, 4047739.3238999993) vs -84.5110375219,34.1409159306
(-9415408.9922, 4042153.4390999973) vs -84.5800580394,34.0993747055
(-9407192.2282, 4056824.275899999) vs -84.5062455928,34.2084354887
(-9393789.5081, 4073006.563000001) vs -84.3858469091,34.3285686841
(-9400678.5072, 4054922.275899999) vs -84.4477318415,34.1943042549
(-9405446.006, 4073268.1525000036) vs -84.4905590118,34.3305092487
(-9405612.508, 4060697.341799997) vs -84.4920547247,34.237203768
(-9402833.8929, 4043210.709399998) vs -84.4668956256,34.1072251709

In [25]:

    

df


    

    
Out[25]:






  

    

      

      
X
      
Y
      
OBJECTID
      
StationNum
      
StationNam
      
FullAddr
    
  
  

    

      
0
      
-84.289385
      
34.236529
      
3
      
4
      
Holbrook Campground - Hwy 372
      
9253 Freehome Highway Canton 30114
    
    

      
1
      
-84.358910
      
34.250171
      
4
      
5
      
Circle Five
      
1037 East Cherokee Drive Canton 30115
    
    

      
2
      
-84.499132
      
34.173021
      
7
      
8
      
Holly Springs
      
260 Hickory Road Canton 30115
    
    

      
3
      
-84.493214
      
34.263119
      
8
      
9
      
Old North Canton
      
1398 Reinhardt College Parkway Canton 30114
    
    

      
4
      
-84.554572
      
34.315684
      
11
      
12
      
Waleska City
      
9081 Fincher Road Waleska 30183
    
    

      
5
      
-84.610706
      
34.219417
      
12
      
13
      
Sutallee - Hwy 20 West
      
2833 Knox Bridge Highway Canton 30114
    
    

      
6
      
-84.277798
      
34.364988
      
14
      
15
      
Mica
      
5804 Yellow Creek Road Ball Ground 30107
    
    

      
7
      
-84.590606
      
34.326570
      
16
      
17
      
Lake Arrowhead - Front Gate
      
125 Chickasaw Drive Waleska 30183
    
    

      
8
      
-84.627457
      
34.394869
      
17
      
18
      
Salacoa
      
5840 Salacoa Road Waleska 30183
    
    

      
9
      
-84.423085
      
34.290502
      
19
      
21
      
Ball Ground Industrial
      
1190 Evenflo Drive Ball Ground 30107
    
    

      
10
      
-84.544785
      
34.183099
      
20
      
22
      
Bridgemill
      
9550 Bells Ferry Road Canton 30114
    
    

      
11
      
-84.416191
      
34.166260
      
21
      
23
      
Hickory Flat - Vaughn Road
      
7625 Vaughn Road Canton 30115
    
    

      
12
      
-84.380567
      
34.173439
      
23
      
32
      
Hickory Flat - Sugar Pike Rd
      
3644 Sugar Pike Road Canton 30115
    
    

      
13
      
-84.584434
      
34.139196
      
26
      
20
      
Oak Grove - Bells Ferry Hobgood
      
6724 Bells Ferry Road Woodstock 30189
    
    

      
14
      
-84.623231
      
34.095792
      
27
      
19
      
Oak Grove - Hwy 92 West
      
100 Ridge Mill Court Acworth 30102
    
    

      
15
      
-84.493035
      
34.194864
      
1
      
99
      
Headquarters
      
150 Chattin Drive Canton 30115
    
    

      
16
      
-84.271186
      
34.212539
      
25
      
25
      
Holbrook Campground Rd
      
2250 Holbrook Campground Road Alpharetta 30004
    
    

      
17
      
-84.607935
      
34.305191
      
29
      
27
      
Lake Arrowhead Vfd - Rear Gate
      
7010 Great Festival Trl Waleska 30103
    
    

      
18
      
-84.370601
      
34.380850
      
30
      
26
      
Nelson City
      
89 Dogwood Pass Nelson 30107
    
    

      
19
      
-84.511038
      
34.140916
      
24
      
24
      
Woodstock/South Holly Springs
      
1000 Riverpark Boulevard Woodstock 30188
    
    

      
20
      
-84.580058
      
34.099375
      
18
      
1
      
Oak Grove
      
100 Old Bascomb Ct Acworth 30102
    
    

      
21
      
-84.506246
      
34.208435
      
10
      
11
      
CCFES South Canton
      
2371 Marietta Highway Canton 30115
    
    

      
22
      
-84.385847
      
34.328569
      
2
      
2
      
Ball Ground
      
420 Valley St Ball Ground 30107
    
    

      
23
      
-84.447732
      
34.194304
      
22
      
3
      
Hickory Flat - 3
      
270 Crown Ave Canton 30115
    
    

      
24
      
-84.490559
      
34.330509
      
5
      
6
      
Clayton Community
      
3396 Land Road Canton 30114
    
    

      
25
      
-84.492055
      
34.237204
      
46
      
16
      
Canton City Downtown
      
190 West Main Street Canton 30114
    
    

      
26
      
-84.466896
      
34.107225
      
6
      
7
      
Little River
      
1530 Barnes Road Woodstock 30188
    
  

In [45]:

    

map(lambda x: x.get('geom').coords, FireStation.objects.filter(department_id=77286).values('geom', 'station_number'))


    

    
Out[45]:





[(-84.58005803935441, 34.0993747054522),
 (-84.62323123632191, 34.095792181300205),
 (-84.2711860371648, 34.2125394807089),
 (-84.385846909144, 34.3285686841128),
 (-84.4230853956422, 34.2905017450316),
 (-84.2777984388736, 34.364988054372894),
 (-84.37060105281749, 34.380849838821),
 (-84.5447849108352, 34.1830993862808),
 (-84.4920547246762, 34.2372037680391),
 (-84.5062455928134, 34.2084354887498),
 (-84.2893848662313, 34.2365294032759),
 (-84.3589104510761, 34.2501706845755),
 (-84.4905590117725, 34.3305092487104),
 (-84.49303514881059, 34.1948641779575),
 (-84.447731841461, 34.1943042549195),
 (-84.3805670456389, 34.1734388108431),
 (-84.4161913665757, 34.166260005525),
 (-84.4991319292805, 34.1730208321629),
 (-84.493213992319, 34.2631194789143),
 (-84.6107060861765, 34.2194174634246),
 (-84.5545718400793, 34.3156841291121),
 (-84.5906056071791, 34.3265697502822),
 (-84.6079347593216, 34.3051914536837),
 (-84.6274566765919, 34.3948693327083),
 (-84.46689562564609, 34.1072251708984),
 (-84.58443374409359, 34.1391963196169),
 (-84.51103752188509, 34.1409159305985)]

In [ ]: