- What is the purpose of a specific SDI datatypes in SQL tables?
- They could be represented as "(3,1)" (in case of point on a plane) VARCHAR and then retrieved by the application and processes them?
- What is the difference between Geometrical and Geographical datatypes?
- Please mention applications of SDI types with SQL source code for better understanding.
- Is there any sample database (preferably for postgreSQL) that demonstrates the use of these data types as the learner plays with the data to better understand their utility?
closed as too broad by mustaccio, RLF, RolandoMySQLDBA, Andriy M, Shanky Jun 14 '16 at 7:21
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1) The purpose is to provide a logical representation of a data entity (coordinates for example or a path) that can be indexed and queried in Postgresql. And also postgresql can understand this datatype.
2) Technically it's possible but you're better of storing it as two float so you can filter on them. It's never recommended to try to outsmart database datatypes. For example how would you query all datapoints that are in ((1,3), (7,9)) bounding box if you store that as VARCHAR. This would become incredibly complex and suboptimal to manage in application level.
3) Putting it very simple: geometrical data represents a normal cartesian coordinate pane where dimensions are straight lines in space. Geographical is latitude, longitude based and using geodetic calculation. The difference gets more obvious when you start to talk about distance between points and the variability between distance.
In planar calculation the distance between (1,1) and (2,2) are the same as (101,101) and (202,202). While in geodetic the distance varies big times depends how far are you from equator.
This was answered in more details in another answer on gis stackoverflow: https://gis.stackexchange.com/questions/6681/what-are-the-pros-and-cons-of-postgis-geography-and-geometry-types
create table something ( id serial PRIMARY KEY, p point not null ); insert into something (p) values (point(1,2)), (point(5,6)), (point(123,32)); select * from something where p <@ BOX(POINT(1,1), POINT(2,2)); id | p ----+------- 1 | (1,2) (1 row)
5) I would just start playing around with queries, inserting and querying data to find the power of these datatypes.