Modify Location Schema to be able to distinguish between cities and country sub-dvisions

[simonb83]

The extracted location could be either a Country, a country sub-division (i.e. province or state) or a City.

We need to modify the schema for Location class in model.py to ensure that we can capture these different options:

CREATE TABLE location ( id SERIAL PRIMARY KEY,    description TEXT,    city TEXT,  
state TEXT,  country CHAR(3) REFERENCES country ON DELETE CASCADE, latlong TEXT );

[alexanderrich]

Hi @simonb83 , I'm new to this project and would like to take a crack at this. Just to make sure I understand what's required, the needed changes are:

• update shema.sql in the manner described
• update the location class in model.py to match the new schema
• update the test_location function in test_model.py to include an example with city and/or state values Is that about right?

[WanderingStar]

@alexanderrich, yes, that's right!

[frenski]

Btw, as we are currently changing the schema, why don't we split the latlong field it into two different ones (lat and lon) and keep them as double? It would be both and faster end easier for the frontend?

[lematt1991]

Since you guys are already using Postgres, I might suggest looking at the PostGIS extension [1]. This adds native spatial types (Point, Polygon, Line, etc.). You could store the lat/lon as a Point which will not only allow you to easily extract the numerical lat/lon values, but also perform spatial queries on the table, such as finding all points within a polygon, nearest points, etc.

[WanderingStar]

Will that field be used? Where will the data come from? In what format? I punted on making a decision about how to store that... latlong = Column(String) # Not tackling PostGIS right now ...because I thought it was unlikely to be used in the short run, and didn't want to commit to a format (decimal degrees? degrees decimal minutes? DMS?) or get PostGIS set up on the Amazon RDS. But if it seems like it will be useful for the first version of the site and someone is interested in submitting a pull request, by all means!

[simonb83]

I agree that right now the lat-lon fields are unlikely to be used in v1 so it is more likely to be a future enhancement at some point down the line. Don't know if that helps make the decision...