How to handle an address base [closed]

Navigation
1

I have a database where the CPF is the PK and there is address information (such as zip code, street address, number, etc.). I would like to find people who live in the same residence, comparing street and number. However, there are situations in which the names of the sites have been entered in a similar way, but not the same. Example: "Avenida Paulista" and "Av. Paulista". This makes it difficult to treat the base. Would anyone have any suggestions to help? Thank you very much in advance!! =)

    
asked by anonymous 18.12.2018 / 01:25

2 answers

1

I believe you need to use the LIKE command in your sql query. As there are different forms for the text inserted in the public domain column, you could make use of the command mentioned above, follow the example below: 

SELECT *campo1, campo2...*, FROM *table* WHERE UPPER(logradouro) LIKE UPPER ('%Av%paulista%')

Or 

SELECT *campo1, campo2...*, FROM *table* WHERE LOWER(logradouro) LIKE LOWER('%Av%paulista%')
    
18.12.2018 / 01:51
0

If you have Pandas installed (Pandas is a database library written and compatible with Python), you can do this with the following code: 

import pandas as pd


Dicionario_BancoDeDados = {'123.456.789-10' : {'CEP': '11.111-000', 'Logradouro': 'Av. Paulista', 'Numero': 99},
                           '123.456.789-11' : {'CEP': '11.111-001', 'Logradouro': 'Av. Paulista', 'Numero': 99},
                           '123.456.789-12' : {'CEP': '11.111-002', 'Logradouro': 'Av. Distante', 'Numero': 99}}


BancoDeDados = pd.DataFrame.from_dict(Dicionario_BancoDeDados).T     # .T é a operação de transposição


BancoDeDados

Youcanfindpeoplewholiveinthesameresidencebyrunning:

VizinhosDePredio=BancoDeDados[BancoDeDados.duplicated(['Logradouro','Numero'],keep=False)==True]VizinhosDePredio

Inordertocircumventdubitiesinstreetnames,Isuggestyoudosobeforeyousearchforneighbors.I'llshowyouonesuchimplementationbelow:

Dicionario_BancoDeDados={'123.456.789-10':{'CEP':'11.111-000','Logradouro':'Av.Paulista','Numero':99},'123.456.789-11':{'CEP':'11.111-001','Logradouro':'AvenidaPaulista','Numero':99},'123.456.789-12':{'CEP':'11.111-002','Logradouro':'Av.Distante','Numero':99}}BancoDeDados=pd.DataFrame.from_dict(Dicionario_BancoDeDados).TdefAbreviarLogradouro(Logradouro):Logradouro=Logradouro.replace('Avenida','Av.')Logradouro=Logradouro.replace('Rua','R.')returnLogradouroBancoDeDados['Logradouro']=BancoDeDados['Logradouro'].map(lambdax:AbreviarLogradouro(x))BancoDeDados

    
18.12.2018 / 06:12
foreach selective two tables Help with select in MYSQL [closed]