Mapping State-Level Data

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • Answered
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.
Photo of Intrep

Intrep

  • 62 Points

Posted 9 months ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,302 Points 20k badge 2x thumb
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.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
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.
Photo of Intrep

Intrep

  • 62 Points
Thank you Dan & Mark!! I will be trying this later today and will post back.
Photo of Intrep

Intrep

  • 62 Points
Dan, Mark -- That worked like a charm. Thank you both!!
Cheers, Dan
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,302 Points 20k badge 2x thumb
I am gonna make a Service Worker version that will  be even better!