Separate Site address into separate fields for address, city, state_province, postal_code

[willbuckner]

NetBox version

v3.2.5

Feature type

Change to existing functionality

Proposed functionality

Currently, Netbox has a single free-form address field for sites. When external systems use Netbox as a source of truth, and those external systems require separate fields for City, State/Province, Zip/Postal Code, it becomes incredibly difficult to use Netbox's data for these integrations. It's trivial to go from separate fields to a single address field, but quite difficult to reverse the process.

Proposed change is to add the following fields to the site model:

• city
• state_province
• postal_code

To avoid further complicating the data model, I'd propose that we leave the freeform address field, where a user is free to add as many lines as they want to the address (rather than splitting out an address1, address2, address3, etc).

To make migration easier, the 3 new fields could be optional.

Use case

Though Netbox doesn't use or validate this data, Netbox is often the system of record for managing all data related to physical site locations. The inability to feed its address information into other systems which require separate fields complicates the overall data model around sites for larger organizations with hundreds of sites and multiple systems managing this data (for example, logistics software, accounting software, operational systems).

Database changes

Add the following fields to the site model:

• city
• state_province
• postal_code

External dependencies

No response

[martinum4]

Keeping the current address field for street or equivalent and not trying to split it up further seems reasonable, especially considering how much edge-cases there are in regard to naming/numbering schemes.

[sdktr]

When in need of the address components (because our naming convention is based on the city UN/LOCODE standard) we use the google maps address API. Usage of this API requires a $0 google subscription though

[jeremystretch]

We specifically avoided adding discrete address fields in the early days because they are not universal. I'd be willing to introduce additional fields only if we can cite some well-defined standard dictating what they should be.

[DanSheps]

What if we did the same thing we did for contacts, break it out into a separate model for:

Address State/Province/prefecture/district Country Postal/Zip code Latitude Longitude

I think most places have that from what I can remember but I can also take a look and see if I can find a semi-well defined standard. Pretty much replicating the fields on a certificate.

This would also allow both address re-use if needed and adding to additional models (maybe for locations?)

[sleepinggenius2]

First off, I would love to see this broken out into a separate model, ideally like contacts, where they can be assigned to other objects with a role. The current Site model includes both a Physical address and a Shipping address, but we certainly run into situations where we may have an official E-911 address for a given location and the backhaul or power provider may have a different service address in their records, which would all be nice to be able to capture. Extending this to at least Locations would be really helpful too, as I've already had to add a custom field to our Location model to capture address information.

I think part of what you are looking for regarding a standard are called administrative boundaries and they are assigned different levels. Here's a good listing for how they are applied in a number of countries: https://wiki.openstreetmap.org/wiki/Tag:boundary%3Dadministrative and in the United States specifically: https://wiki.openstreetmap.org/wiki/United_States_admin_level. In your example, Country would be admin_level=2 and State/Province/Prefecture/District would be admin_level=4 in most places. We are currently required to keep track of additional administrative levels (like the county or independent city) of our sites for tax purposes, which I imagine is a situation that a number of other companies run in to as well.

Typically, postal/zip codes are orthogonal to the administrative boundary levels and would need to be tracked independently.

[DanSheps]

Thanks for the information @sleepinggenius2, however administrative boundaries looks to be a wikipedia construct and not a formally defined standard.

However, I like the idea and perhaps it is something we could do, call them boundaries and expose them as customizable in the config.

For example, you could have:

BOUNDARIES = {
    "level-1": None,
    "level-2": "Country",
    "level-3": None,
    "level-4": "State",
    "level-5": None,
    "level-6": "County",
    "level-7": "Township",
    "level-8": "City",
    "level-9": "Borough",
    "level-10": "Neighborhood",
}

For a more US centric look.

Canada might look more like:

BOUNDARIES = {
    "level-1": None,
    "level-2": "Country",
    "level-3": None,
    "level-4": "Province",
    "level-5": "Region",
    "level-6": "Municipality",
    "level-7": "Town/City",
    "level-8": "Neighbourhood",
    "level-9": None,
    "level-10": None,
}

"None" would not be rendered as part of the form

[sleepinggenius2]

The closest thing I could find to a standard related to this is ISO3166, where ISO3166-1 defines the country country codes and ISO3166-2 defines the codes for principal subdivisions of the countries. Beyond that point, any additional boundaries/divisions/levels seems to be primarily up to the individual countries. For example, in the United States, the Census Bureau maintains some of the data (at least down to counties): https://www.census.gov/programs-surveys/geography/technical-documentation/naming-convention/cartographic-boundary-file.html.

While it may not be an official standard, the use of administrative boundaries/divisions/levels is certainly a concept that I've seen in every GIS/geo-spatial system that I've worked with (previous example from OpenStreetMap).

I've been thinking more about an implementation for this and the static config seems a bit limiting, especially with multinational organizations, and changing a value in the config now potentially invalidates all the data in the database, so you would never be able to change the structure once data is populated. This led me to the idea of implementing boundaries as a separate object in the database itself, which could then be manipulated as needed without affecting data integrity. Then that just started sounding a lot like the existing Region model, which I think could be leveraged for this purpose, and a lot of organizations are probably using it in a similar structure anyway (I know we are). I think if we just added a Region Type model that could be associated to the existing Region model, that would cover calling a particular region whatever type/boundary/division/level you wanted. That would also provide the additional UX advantage of not having to redefine the same data for every address; just pick the city and you automatically have the county/municipality, region, state/province, etc. for free.

One complication that I see is that I know in the US we have cities that can exist in multiple counties, so they may need to be duplicated in the data model to support this kind of structure. I know that would cause an issue with the way that we have some of our slug values defined today and may need to move that data to custom fields (we were already considering that anyway). That would also necessitate seeing the full path to a region or otherwise be able to distinguish multiple with the same name in a dropdown. Ideally, that's something I would like to see in general, as it's already an issue in a number of dropdowns today.

[brandenrager]

The OASIS "extensible Address Language" (xAL) seems to be popular for other projects. https://www.oasis-open.org/committees/ciq/download.html (search xAL)

Google seems to have implemented it, and has a open-source metadata that allows for better validation. The code linked on item 4 has some nice documentation on why they used it.

Adding an address role/type would be beneficial to differentiate between physical, delivery, E911, etc.

Research path:

1. https://github.com/google/libaddressinput
2. https://github.com/google/libaddressinput/wiki/AddressValidationMetadata
3. https://github.com/google/libaddressinput/blob/master/android/src/main/java/com/android/i18n/addressinput/PlaceDetailsApi.java
4. https://github.com/google/libaddressinput/blob/master/common/src/main/java/com/google/i18n/addressinput/common/AddressData.java

[jcollie]

IMHO, trying to create a formal model for addresses is creating more work than it's worth for NetBox. I'd say leave the addresses as a free-form box. If individual implementations require something more they can add it using custom fields or a plugin, or just by using a service like Google or Melissa to verify/standardize the address.

[jeremystretch]

The inability to feed its address information into other systems which require separate fields complicates the overall data model around sites for larger organizations with hundreds of sites and multiple systems managing this data.

How would the proposed change solve this? You would still need some translation logic unless every field matched the peer system exactly. And in the event you have two such systems which employ differing address formats - which is extremely likely - you obviously can't map directly to both. IMO the burden for this mapping should fall on whatever integration is moving the data, as it's likely to be needed regardless of what format we'd choose for NetBox.

[willbuckner]

The inability to feed its address information into other systems which require separate fields complicates the overall data model around sites for larger organizations with hundreds of sites and multiple systems managing this data.

How would the proposed change solve this? You would still need some translation logic unless every field matched the peer system exactly. And in the event you have two such systems which employ differing address formats - which is extremely likely - you obviously can't map directly to both. IMO the burden for this mapping should fall on whatever integration is moving the data, as it's likely to be needed regardless of what format we'd choose for NetBox.

It would solve this because parsing freeform addresses is extremely difficult--it's pretty easy to turn a structured address back into a freeform address if another system requires that, and easy to have confidence that things like country/city/state/province are correct when they're each in separate fields.

I agree that mapping is the responsibility of the integration, but we don't have a way to build such an integration currently, without trying to account for every possible edge case of a freeform address. The only route I see us able to go down without splitting up the address fields in Netbox would be to use custom fields for addresses, which would make the actual site display in Netbox quite a mess, or require us to duplicate data manually in Netbox.

Additionally, I'd love to query Netbox sites by country, for example, which we can't currently do with the address format we have.

[jeremystretch]

Additionally, I'd love to query Netbox sites by country, for example, which we can't currently do with the address format we have.

The region model serves this function, and is much more powerful than matching by a static field value.

[willbuckner]

Additionally, I'd love to query Netbox sites by country, for example, which we can't currently do with the address format we have.

The region model serves this function, and is much more powerful than matching by a static field value.

Not unless I add a region for every country. This would make sense when managing a small number of sites across relatively few countries, with new sites being added infrequently, however this is not our use case. I suppose we could pre-fill sub regions for every country, and every state/province within those countries, but I still believe we'd have a cleaner data model if we could just store postal code, state/province, and country in separate fields.

[jcollie]

It would solve this because parsing freeform addresses is extremely difficult--it's pretty easy to turn a structured address back into a freeform address if another system requires that, and easy to have confidence that things like country/city/state/province are correct when they're each in separate fields.

It's difficult because every country does things differently and not every country maps neatly into city/state/province. There may be other administrative levels that you want to include like prefecture/sub-prefecture or counties/parishes, etc. Or things like "Midwest"/"New England" for the US. Street addresses and postal codes/zip codes are also very different country-to-country. I think that you're much better off either creating a hierarchy of regions or adding your own custom fields to sites.

[BarbarossaTM]

I like the idea of using a Region hierarchy to model continent -> "region" (for lack of a better word) -> country -> ... -> city.

I believe it would be beneficial to have an attribute like "scope" for a Region to denote what level of Region it is (continent vs. city for example). Although this could be added as a custom fields, I guess it might make more sense to have that ready inside NetBox.

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. NetBox is governed by a small group of core maintainers which means not all opened issues may receive direct feedback. Do not attempt to circumvent this process by "bumping" the issue; doing so will result in its immediate closure and you may be barred from participating in any future discussions. Please see our contributing guide.

[github-actions[bot]]

This issue has been automatically closed due to lack of activity. In an effort to reduce noise, please do not comment any further. Note that the core maintainers may elect to reopen this issue at a later date if deemed necessary.