Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Combine multiple counties on a map into reqions

  • 1.  Combine multiple counties on a map into reqions

    Posted Dec 07, 2018 06:23 PM

    PowerBI Community,

     

    I have succeeded in downloading a JSON file to display just my state and create visuals by county.  Is there now a way that I can combine multiple counties into region?

     

    Andrew R. Parker

    Lead Medicaid Research Analyst

    Office of Health Analytics

    Oregon Heath Authority

    andrew.r.parker@dhsoha.state.or.us

    503-559-5194

     

    Conference-PBI_200x200


  • 2.  RE: Combine multiple counties on a map into reqions

    Bronze Contributor
    Posted Dec 10, 2018 03:16 AM
    Hi Andrew,

    I am not sure if this would work for you or not, but have you tried "Grouping" the counties using the Power BI Grouping functionality?  That is what I would try.

    Alternatively, if your regions happen to match what Bing thinks are regions then you could add a Region column to your data and define this in Power BI as an Address/Region field.

    Regards,

    Hugh

    ------------------------------
    Hugh Johnson
    Dublin
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Combine multiple counties on a map into reqions

    Posted Dec 10, 2018 11:25 AM
    Regions are custom so are likely not in Bing.  Will try Grouping functionality.​

    ------------------------------
    Andrew Parker
    Research Analyst
    Salem OR
    503-559-5194
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Combine multiple counties on a map into reqions

    Silver Contributor
    Posted Dec 10, 2018 07:06 AM
    Use mapshaper.com or similar to combine your shapes. On Mapshaper, use the "dissolve"  command but make sure you keep the region attribute.

    ------------------------------
    Lutz
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Combine multiple counties on a map into reqions

    Posted Dec 10, 2018 11:26 AM
    Thanks Lutz - I will try mapshaper.com.​

    ------------------------------
    Andrew Parker
    Research Analyst
    Salem OR
    503-559-5194
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: Combine multiple counties on a map into reqions

    Bronze Contributor
    Posted Dec 10, 2018 10:25 AM
    Hi Andrew -

    This is a timely discussion.  I am looking for a good source for these JSON files - in particular, by state and zip code.  Can you point me to the source you used for the JSON download? Similarly, i would then like to group certain zip codes into custom regions so i will continue to follow this thread.  Thanks!

    Chris

    ------------------------------
    CHRISTOPHER MAJKA
    MANAGER, PRODUCT ANALYTICS
    MAPFRE
    8002211605
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: Combine multiple counties on a map into reqions

    Silver Contributor
    Posted Dec 10, 2018 11:20 AM

    1.      Download ZIP code shape polygons from TIGER

    Open a browser to: https://www.census.gov/cgi-bin/geo/shapefiles2010/main

    Choose 'Zip Code Tabulation Areas' and 'Submit'

    Choose 'All states in one national file' for '5-Digit ZIP Code Tabulation Area (2010)' and click 'Download'

    2.      Unzip the file

    3.      Convert file to geojson format using the gdal library on Windows Subsystem For Linux or other Debian based subsystems
    ogr2ogr -f "GeoJSON" zcta5.geo.json ./tl_2010_us_zcta510.shp tl_2010_us_zcta510

    NOTE:  Especially for western states, but also for some bodies of water in the eastern part you will notice gaps in the polygons (areas without ZIP code coverage).  You may need to manually adjust the GeoJSON in those cases.

    NOTE2:  Power BI expects TopoJSON format (relative addressing) rather than Geo JSON (absolute addressing).  Convert with your program of choice.



    ------------------------------
    Lutz
    ------------------------------

    Conference-PBI_200x200


  • 8.  RE: Combine multiple counties on a map into reqions

    Bronze Contributor
    Posted Dec 10, 2018 03:31 PM
    This has all been very helpful.  I was able to use the census.gov link above to get the shape files downloaded, and then created a TopoJSON file for each state i needed using mapshaper.org.

    Next step is to create custom 'regions' based on zip

    Thanks again!

    ------------------------------
    CHRISTOPHER MAJKA
    MANAGER, PRODUCT ANALYTICS
    MAPFRE
    8002211605
    ------------------------------

    Conference-PBI_200x200


  • 9.  RE: Combine multiple counties on a map into reqions

    Silver Contributor
    Posted Dec 10, 2018 04:28 PM
    I did that for my scenario by injecting the necessary attributes into the json (looking up the region code for each zip file etc) via a little script, and then using that additional attribute in mapshaper. Remember to keep the attribute you "dissolve"  by, like

    mapshaper -dissolve PSA -copy-fields=PSA

    ------------------------------
    Lutz
    ------------------------------

    Conference-PBI_200x200


  • 10.  RE: Combine multiple counties on a map into reqions

    Silver Contributor
    Posted Dec 11, 2018 09:15 PM
    Edited by Tony McGovern Dec 12, 2018 12:13 PM
    @Lutz Bendlin, the tools you recommend to manipulate geographic layers and tilesets are the ones I use as well. Really great stuff. Did you know ogr2ogr has an API where you can transform Shapefiles to geojson using POST requests? I've been playing around with an M function that automatically downloads shapefiles and converts them to geojson using the API. Not there quite yet, but it's a work in progress.

    @CHRISTOPHER MAJKA Somewhat relatedly, I put together an M function that connects to census.gov and returns all the geographic areas from their Gazetteer files. It gives you the ability to download information on all geographic types by year. The function really helps me normalize and tidy my location data. You can find it here.

    Here's what it does:

    Here's the code (just copy and paste this into the Advanced Editor of a new Blank Query):

    let
    	Census.GetGeographicAreas = (year as text, geography as text) as table =>
    		let
                UnzipContents = Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/tonmcg/powerCensus/master/M/UnzipContents.pq")), #shared),
                Geographies = Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/tonmcg/powerCensus/master/M/Census.Geographies.pq")), #shared),
                GetStates = Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/tonmcg/powerCensus/master/M/Census.GetStates.pq")), #shared),
                returnTable = 
                    if geography = "State" then 
                        GetStates(true) 
                    else
    					let
    						geographies = Geographies,
    						getGeographyNames = (n as number) =>
    							let
    								geography = geographies{n}[geography]
    							in
    								if n = List.Count(geographies) then
    									{}
    								else
    									List.Combine({{geography}, @getGeographyNames(n + 1)}),
    						geographyNames = getGeographyNames(0),
    						// Congressional Districts follow a different url pattern from other geographies; we'll call these "excepted geographies"
    						// test whether the user selected Congressional District as the geography type
    						isException = List.Contains({"Congressional District"}, geography),
    						// if true, create the url based on the mapping provided in the table below; if false, return null
    
    							/*
    							+--------------------------------+--------+---------+
    							| GEOGRAPHY                        YEAR     MAPS TO |
    							+--------------------------------+--------+---------+
    							  Congressional District           2012     112
    							  Congressional District           2013     113
    							  Congressional District           2014     114
    							  Congressional District           2015     114
    							  Congressional District           2016     115
    							  Congressional District           2017     115
    							  Congressional District           2018     116
    							+--------------------------------+--------+---------+
    							  
    							*/
    
    						// Define the Congressional Districts by the meeting that corresponds to the year
    						// e.g., 2016 was the 115th meeting of Congress
    						CongressionalDistricts = 
    							if isException then
    								List.First(List.Select(geographies, each _[geography] = "Congressional District"))[yearList]
    							else
    								null,
    						// test whether the user selected an excepted geography
    						UrlMap = 
    							if isException then CongressionalDistricts else null,
    						GeoQuery = 
    							if isException then 
    								Text.Combine({List.First(List.Select(UrlMap, each Text.From(_[year]) = year))[meeting], "CDs"})
    							else 
    								List.First(List.Select(geographies, each _[geography] = geography))[gazetteerUrlPart], 
    						GeoColumns = List.First(List.Select(geographies, each _[geography] = geography))[numOfColumns],
    						GeoName = List.First(List.Select(geographies, each _[geography] = geography))[gazetteerUrlPart],
    						// get authoritative list of geographic areas for selected geographic area types 
    						GazetteerFile = Csv.Document(UnzipContents(Web.Contents("http://www2.census.gov/geo/docs/maps-data/data/gazetteer/" & year & "_Gazetteer/" & year & "_Gaz_" & GeoQuery & "_national.zip")){0}[Content],[Delimiter="#(tab)", Columns=Expression.Evaluate(Text.From(GeoColumns)), Encoding=1252, QuoteStyle=QuoteStyle.None]),
    						PromotedGazetteerHeaders = Table.PromoteHeaders(GazetteerFile, [PromoteAllScalars=true]),
    						ColumnSelectList = if List.Contains({"Census Tract", "Congressional District", "ZIP Code Tabulation Area"},geography) then {"GEOID"} else {"GEOID", "NAME"},
    						SelectedGazetteerColumns = Table.SelectColumns(PromotedGazetteerHeaders,ColumnSelectList),
    						ColumnRenameList = if List.Contains({"Census Tract", "Congressional District", "ZIP Code Tabulation Area"},geography) then {{"GEOID", "geoid"}} else {{"GEOID", "geoid"}, {"NAME", "name"}},
    						RenamedGazetteerColumns = Table.RenameColumns(SelectedGazetteerColumns,ColumnRenameList),
    						ColumnNamesList = if List.Contains(Table.ColumnNames(RenamedGazetteerColumns),"name") then RenamedGazetteerColumns else Table.AddColumn(RenamedGazetteerColumns, "name", each [geoid], type text),
    						DefinedGazetteerColumns = {{"geoid", type text}, {"name", type text}},
    						DefinedDataTypes = Table.TransformColumnTypes(ColumnNamesList,DefinedGazetteerColumns),
    						GazetteerTable = DefinedDataTypes,
    						AddedYear = Table.AddColumn(GazetteerTable, "year", each Number.FromText(year), Int64.Type),
    						AddedgeographyType = 
    							Table.AddColumn(
    								AddedYear, 
    								"geography_type", 
    								each if isException then 
    									Text.Combine({List.First(List.Select(UrlMap, each Text.From(_[year]) = year))[meeting], "th ", geography})
    								else
    									geography, 
    								type text
    							),
    						AddedIndex = Table.AddIndexColumn(AddedgeographyType, "index", 1, 1)
    					in
    						AddedIndex
    		in
    			returnTable,
        ParameterTypes = type function(
            year as (
                type text meta [ 
                    Documentation.AllowedValues = {"2012", "2013", "2014", "2015", "2016", "2017","2018" }
                ]
            ),
            geography as (
                type text meta [ 
                    Documentation.AllowedValues = {"State", "Urban Area","Core Based Statistical Area","County","County Subdivision","Place","Census Tract","Congressional District","School Districts - Elementary","School Districts - Secondary","School Districts - Unified","ZIP Code Tabulation Area"}
                ]
            )
        ) as table meta        
        [
            Documentation.Name =  " Census.GetGeographicAreas",
            Documentation.Description = " Return a listing of all geographic areas for selected United States geographic area types.",
            Documentation.LongDescription = " Return a listing of all geographic areas for selected United States geographic area types. The year parameter specifies that year's listing of geographic areas. The geography parameter defines the specific geographic type to return. Geographic areas are updated by the U.S. Census Bureau every year. All geographic types from the U.S. Census Gazetteer Files website (https://www.census.gov/geo/maps-data/data/gazetteer.html) are available.",
            Documentation.Category = " Table.Transform",
            Documentation.Source = " ",
            Documentation.Author = " Tony McGovern: www.emdata.ai",
            Documentation.Examples = {
                [
                    Description =  "Return a listing of all United States counties as of 2017.", 
                    Code = " GetGeographicAreas(""2017"",""County"")", 
                    Result = "Table.FromRecords({[geoid = ""01001"",name = ""Autauga County"",year = 2017,geography_type = ""County"",index = 1],[geoid = ""01003"",name = ""Baldwin County"",year = 2017,geography_type = ""County"",index = 2],[geoid = ""01005"",name = ""Barbour County"",year = 2017,geography_type = ""County"",index = 3],[geoid = ""..."",name = ""..."",year = ""..."",geography_type = ""..."",index = ""...""],[geoid = ""72153"",name = ""Yauco Municipio"",year = 2017,geography_type = ""County"",index = 3220]})"
                ]
            }
        ] 
    in 
        Value.ReplaceType(Census.GetGeographicAreas, ParameterTypes)
    ​​​

    ------------------------------
    Tony McGovern
    co-Founder & Data Scientist
    Emdata Inc.
    2025946194
    ------------------------------

    Conference-PBI_200x200


  • 11.  RE: Combine multiple counties on a map into reqions

    Bronze Contributor
    Posted Dec 12, 2018 08:59 AM
    @Tony McGovern Thank you for sharing - this looks amazing.  Looking forward to digging into this!​​​

    ------------------------------
    CHRISTOPHER MAJKA
    MANAGER, PRODUCT ANALYTICS
    MAPFRE
    8002211605
    ------------------------------

    Conference-PBI_200x200


  • 12.  RE: Combine multiple counties on a map into reqions

    Posted Dec 13, 2018 09:34 AM
    ​When I try to run the code you provided I get the below error:

    An error occurred in the '' query. DataSource.Error: Web.Contents failed to get contents from 'https://www2.census.gov/geo/docs/maps-data/data/gazetteer/2017_Gazetteer/2017_Gaz_counties_national.zip?sec_ak_reference=18.bc129941.1544711576.4cac72c4' (503): Service Unavailable
    Details:
        DataSourceKind=Web
        DataSourcePath=http://www2.census.gov/geo/docs/maps-data/data/gazetteer/2017_Gazetteer/2017_Gaz_counties_national.zip
        Url=https://www2.census.gov/geo/docs/maps-data/data/gazetteer/2017_Gazetteer/2017_Gaz_counties_national.zip?sec_ak_reference=18.bc129941.1544711576.4cac72c4

    ------------------------------
    Micheal Goodman
    Crisis Management Officer

    ------------------------------

    Conference-PBI_200x200


  • 13.  RE: Combine multiple counties on a map into reqions

    Silver Contributor
    Posted Dec 13, 2018 09:56 AM
    A 503 error means that their server is overloaded. I've gotten that error once or twice before.

    On those occasions where I get that error, I hit refresh and the problem goes away. If you get the error again, wait a few seconds more and then hit refresh one more time.

    Let me know if that doesn't work for you.

    ------------------------------
    Tony McGovern
    co-Founder & Data Scientist
    Emdata Inc.
    2025946194
    ------------------------------

    Conference-PBI_200x200


  • 14.  RE: Combine multiple counties on a map into reqions

    Posted Dec 10, 2018 11:45 AM
      |   view attached
    ​I can't find the location of json zip file for us counties that I downloaded but here it is.  Very slick, but you need to make sure you have your FIPS codes formatted correctly as 3-digit string.  Don't have anything for zips.

    ------------------------------
    Andrew Parker
    Research Analyst
    Salem OR
    503-559-5194
    ------------------------------

    Attachment(s)

    zip
    topojson-master.zip   4.89MB 1 version
    Conference-PBI_200x200


  • 15.  RE: Combine multiple counties on a map into reqions

    Gold Contributor
    Posted Dec 12, 2018 11:49 AM
    I have been following this discussion with great interest and am hoping to find a public source for parcel/lot lines for my rural (central Georgia) of the USA. Already browsed through US government websites, github, etc... any thoughts? TIA, DannyD

    ------------------------------
    Danny Dennison, MCSA
    http://bit.ly/MCSA_BI
    Business Intelligence Analyst
    Reynolds Lake Oconee
    Greensboro, GA
    7064671624
    ------------------------------

    Conference-PBI_200x200


  • 16.  RE: Combine multiple counties on a map into reqions

    Silver Contributor
    Posted Dec 12, 2018 12:12 PM
    Danny - Have you tried your county's real property taxes website? I believe most counties in the U.S. collect taxes on real property units; many publish this information in publicly-available databases, including geographic info, on their websites.

    Tony

    ------------------------------
    Tony McGovern
    co-Founder & Data Scientist
    Emdata Inc.
    2025946194
    ------------------------------

    Conference-PBI_200x200


  • 17.  RE: Combine multiple counties on a map into reqions

    Bronze Contributor
    Posted Dec 12, 2018 03:13 PM
    Hi Danny -

    Have you tried the TIGER/Line Shapefiles link?  I used that to download the state by zip code shape files, but they also show voting districts, urban growth areas, civil divisions, etc.

    2010 TIGER/Line® Shapefiles
    Census remove preview
    2010 TIGER/Line® Shapefiles
    In response to customer requests, we have redesigned the download interface for the 2010 TIGER/Line Shapefiles. Select the layer you are interested in from the dropdown menu and click 'submit', and you will then see the geographic areas for which that layer is available.
    View this on Census >



    Once you download the appropriate zip files then you have to convert the files at mapshaper.org to a TopoJSON format to be consumed in PowerBI.

    Cheers

    ------------------------------
    CHRISTOPHER MAJKA
    MANAGER, PRODUCT ANALYTICS
    MAPFRE
    8002211605
    ------------------------------

    Conference-PBI_200x200


  • 18.  RE: Combine multiple counties on a map into reqions

    Gold Contributor
    Posted Dec 19, 2018 11:31 AM
    Sorry for the late reply... 'tis the season! :-)

    @CHRISTOPHER MAJKA, I had previously browsed through the TIGER/Line files, but did not find any detailed with individual property boundaries.

    @Tony McGovern, I'm wasn't sure how to go about converting local ("QPublic") maps, however I did find a very detailed "zoning "pdf on our county's website (http://www.greenecountyga.gov/clientuploads/departments/building/Greene_County_Official_Zoning_Map_2018-12-11.pdf), which I was able to convert to shapefile/TopoJSON​​ using https://mygeodata.cloud/converter/.​

    Anyway, thank you both for your replies! DannyD

    ------------------------------
    Danny Dennison, MCSA
    http://bit.ly/MCSA_BI
    Business Intelligence Analyst
    Reynolds Lake Oconee
    Greensboro, GA
    7064671624
    ------------------------------

    Conference-PBI_200x200


  • 19.  RE: Combine multiple counties on a map into reqions

    Posted Dec 24, 2018 05:59 PM
    ​OK - PowerBI experts I think I am close to cracking the code on Mapshaper.org to solve my ongoing challenge of making regions from counties.  But I need one more bit of help to get me over the hump.

    My mapshaper syntax looks like this:
    mapshaper counties.shp \
    -join districts.csv keys=Name,Name \
    -dissolve District \
    -o counties2.shp

    counties.shp is my base file. Field "Name" is the name of my counties.  The file districts.csv contains a new field named "District" which is the same for several counties.  The file counties2.csv is intended to contain output which is the combination of the two files.

    When I run this from the mapshaper console I get a maddening error "The -i command cannot be run in the browser"

    Any help from the PowerBI Community would be greatly appreciated.








    ------------------------------
    Andrew Parker
    Research Analyst
    Salem OR
    503-559-5194
    ------------------------------

    Conference-PBI_200x200


  • 20.  RE: Combine multiple counties on a map into reqions

    Posted Nov 10, 2019 04:06 PM
    Edited by riiupw afrass Nov 10, 2019 04:06 PM
    Hello! How can I find out United States zip codes? Is everything ok with this https://worldpostalcode.com/united-states/ site? This is the first time I registered on the Amazon website and I need a zip code to deliver the parcel. And since I live alone, I have no one to ask. Moreover, it is different from my old one from the UK. I hope you will help me, so where can I find it?
    Conference-PBI_200x200