Location based app

Question

I am creating an application that will allow people to publish information in different areas of the city (about 10 cities). Do you know any existing (normalized) databases / datasets that have such information? Or do I need to create it from scratch? Any suggestions for pointers are greatly appreciated!

Answer 1

Normalization in this case is somewhat problematic, because the requirement of atomicity indicates that one value of a given domain is stored in one attribute, and therefore when looking at coordinate systems everything becomes rather fuzzy. There are several options. Each of them is perfectly standardized.

, ( PostgreSQL):

 CREATE TABLE geo_points (
     id bigserial not null unique,
     x bigint,
     y bigint,
     z bigint,
     primary key (x, y, z)
 );

, geo_points.id.

. , , , :

 CREATE TABLE tagged_location (
    id bigserial not null unique,
    user_id int references users(id),
    location point,
    comment text not null,
    primary key (user_id, location)
 );

'(134.22222, 94.4444)', . , .. , , , . , , , , , .

. , . 1NF, 2d 3d . , , , (.. , ). , , , '{134.22222, 94.4444}'

, , , . , , .

Answer 2

( , )