Получить строки из двух таблиц mysql о географических областях
У меня есть две таблицы:
почтовые индексы: id | состояние | район | почтовый индекс (около 40 тыс. записей)
округ: id | состояние | район | округ (478 записей)
Вот пример данных:
Вот снимок обеих таблиц из phpmyadmin:
Вот что я попробовал:
select a.state, a.district,a.zipcode,
b.county from zipcodes a inner join
county b on a.state=b.state and
a.district =b.district
Но он возвращает огромное количество строк, где каждый почтовый индекс дублируется 12 раз.
строки штата и района имеют одинаковые значения. То, что я пытаюсь сделать, это извлечь и повторить:
состояние | район | почтовый индекс | округ
сопоставляя строки штатов и округов из обеих таблиц, так как в округе несколько почтовых индексов.
Я попытался использовать цикл while внутри цикла while, и это не сработало. Я также пытался использовать JOIN при выборе, но это также было неправильно.
Каков наилучший подход, чтобы это произошло?
Решение
select a.state, a.district,a.zipcode,
b.county from zipcodes a inner join
county b on a.state=b.state and
a.district =b.district
Другие решения
Проблема в том, что вы не обращаете пристального внимания на значения ваших таблиц и запросов.
Я предполагаю, что
- штаты разделят страну
- районы делят штат
- округа делят штат
- почтовые индексы разделяют пересечение района и округа
Таблицы содержат строки, где
in state [state] district [district] contains zipcode [zipcode] ...
in state [state] county [county] intersects district [district] ...
и когда вы присоединяетесь к таблицам, вы получаете строки, где
in state [state] district [district] contains zipcode [zipcode] ...
AND in state [state] county [county] intersects district [district] ...
Таким образом, когда район и округ пересекаются, этот район и округ появляются со всеми почтовыми индексами округа. То есть почтовый индекс появляется с районом и многими округами. Но это не то, что вы хотите! Что вы (предположительно) хотите, так это почтовые индексы на пересечении района и округа. Т.е. почтовый индекс со своим округом и округом. Но эта информация не может быть получена из ваших таблиц. Вам нужна другая таблица, чтобы сообщить округу для каждого почтового индекса. Затем вы можете объединить две таблицы почтовых индексов на почтовый индекс.
in state [state] district [district] contains zipcode [zipcode] ...
AND county [county] contains zipcode [zipcode]
Дело в том, что ваши таблицы, вероятно, получены с использованием таблицы, в которой есть район и округ для каждого почтового индекса.
Ситуация может быть более сложной в зависимости от того, верны ли мои предположения. Возможно, почтовые индексы могут пересекать границы округов и / или округов и / или границы штатов.
Это пример двумерных интервалов с проблемами канонической формы, как, например, с временными базами данных, где интервалы являются одномерными.
PS (После написания выше.)
Погуглив «сделать границы района пересеченными почтовыми индексами»:
Район Конгресса — Почтовый индекс Базы данных для …
https://www.zipinfo.com/products/cdz/cdz.htm
Если 5-значный почтовый индекс пересекает границу округа Конгресса,
Вы должны использовать адрес улицы, чтобы определить правильный конгресс
район для любого …
Поиск в Google «может почтовый индекс пересекать строки состояния»:
Почтовый индекс FAQ — от carrierroutes.com
www.carrierroutes.com/ZIPCodes.html
Это число может колебаться
несколько тысяч почтовых индексов в год, в зависимости от …. почтовых индексов редко
пересекают государственные границы, но пересекают линии графства так часто, как 10%.
Это означает, что на самом деле нам нужны более сложные маркированные предположения вместо четвертого. На данный момент я действительно не знаю, какой запрос вы хотите. Может быть, вы хотите, чтобы каждый почтовый индекс и его пересечения округов округов. (Если существует более одного на почтовый индекс, вам нужно идентифицировать пересечения. Может быть, таблица COUNTY уже дает именно эти идентификаторы пересечений штата, района и округа. Хотя нет никакой связи с почтовыми индексами.)
zipcode [zipcode] contains the intersection [id] of
state [state], district [district] and county [county]
Я не знаю, из каких конкретных таблиц вы можете использовать свой запрос, но базовые данные — это просто артефакт того, как были нарисованы границы штатов, округов, округов и почтовых индексов.
Я оставлю вас с полным абзацем из ссылки район-почтовый индекс:
Другие полезные данные …
Используя район Конгресса — почтовый индекс
База данных, вы можете найти каждый почтовый индекс, который лежит частично или
полностью в пределах данного избирательного округа. Или вы можете найти все
районы Конгресса, которые содержат некоторую часть определенного ZIP
код. Вы также можете найти коды городов, округов и округов FIPS
связан с каждым округом конгресса. Или вы можете идентифицировать все
Почтовые индексы, которые находятся в пределах определенного избирательного округа и
также лежат в пределах конкретной MSA / PMSA.