We are currently working on a “locations” API where we would like to leverage the geo.placefinder data; however, I’m having a bit of trouble constructing the query that I would like to use. Perhaps someone could shed some light on the best way to achieve this:
select * from geo.placefinder where city='Clarksburg' and (countrycode='United States' or country='United States') and (statecode='West Virginia' or state='West Virginia')
It looks like the “or” clause is confusing it, since the following returns the correct record:
select * from geo.placefinder where city='Clarksburg' and (country='United States') and (state='West Virginia')
I do realize that a more restrictive UI could prevent the former query from needing to be called, but if someone has a suggestion on how I should be writing the query to check countrycode/country and statecode/state, it would be much appreciated.