Forum Discussion

DanielGreenberg's avatar
DanielGreenberg
Qrew Trainee
8 years ago

Mapping State-Level Data

I am hoping to be able to map the totals from a related table not by street addresses, but by state. So if there are three customers in NJ (in customer table) with 8 orders totaling a quantity of 50 items between them (all in the orders table), I'd like the pin to be located centered in NJ with total quantity being in the pin details. I'm struggling because it appears the built-in map requires (and will only map) full addresses. Am I missing something? Thanks.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    I am sure you can do this with script and using the info in this post you might be able to do it without:

    How To Use Longitude/Latitude with Address Fields.
    https://community.quickbase.com/quickbase/topics/how-to-use-longitude-latitude-with-address-fields

    In either case you will need a table mapping each state to the centroid latitude and longitude:
    State,Lat,Lon
    Alabama,32.7794,-86.8287
    Alaska,64.0685,-152.2782
    Arizona,34.2744,-111.6602
    Arkansas,34.8938,-92.4426
    California,37.1841,-119.4696
    Colorado,38.9972,-105.5478
    Connecticut,41.6219,-72.7273
    Delaware,38.9896,-75.505
    District of Columbia,38.9101,-77.0147
    Florida,28.6305,-82.4497
    Georgia,32.6415,-83.4426
    Hawaii,20.2927,-156.3737
    Idaho,44.3509,-114.613
    Illinois,40.0417,-89.1965
    Indiana,39.8942,-86.2816
    Iowa,42.0751,-93.496
    Kansas,38.4937,-98.3804
    Kentucky,37.5347,-85.3021
    Louisiana,31.0689,-91.9968
    Maine,45.3695,-69.2428
    Maryland,39.055,-76.7909
    Massachusetts,42.2596,-71.8083
    Michigan,44.3467,-85.4102
    Minnesota,46.2807,-94.3053
    Mississippi,32.7364,-89.6678
    Missouri,38.3566,-92.458
    Montana,47.0527,-109.6333
    Nebraska,41.5378,-99.7951
    Nevada,39.3289,-116.6312
    New Hampshire,43.6805,-71.5811
    New Jersey,40.1907,-74.6728
    New Mexico,34.4071,-106.1126
    New York,42.9538,-75.5268
    North Carolina,35.5557,-79.3877
    North Dakota,47.4501,-100.4659
    Ohio,40.2862,-82.7937
    Oklahoma,35.5889,-97.4943
    Oregon,43.9336,-120.5583
    Pennsylvania,40.8781,-77.7996
    Rhode Island,41.6762,-71.5562
    South Carolina,33.9169,-80.8964
    South Dakota,44.4443,-100.2263
    Tennessee,35.858,-86.3505
    Texas,31.4757,-99.3312
    Utah,39.3055,-111.6703
    Vermont,44.0687,-72.6658
    Virginia,37.5215,-78.8537
    Washington,47.3826,-120.4472
    West Virginia,38.6409,-80.6227
    Wisconsin,44.6243,-89.9941
    Wyoming,42.9957,-107.5512
    I obtained this data by following the info in this wikipedia article:

    List of geographic centers of the United States
    the center for a state was found by suspending a cardboard cutout of the state by a string, and then drawing a vertical line from the suspension point. After rotating the cutout 90 degrees and drawing another vertical line from the new suspension point, the intersection of the two vertical lines was used as the geographic center.
    https://en.wikipedia.org/wiki/List_of_geographic_centers_of_the_United_States

    If you need additional ask away.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    So using Dans information you could make a table with 50 states, with the State being the Key field. If you manually key the lat longs into address 1 and address 2, they will map. Get that far and observe a map of the US with 1 pin per state.

    Then you should be able to set up a relationship back to the Orders table and summarize the total Orders and use that on your map pin.