1. What is the purpose of a specific SDI datatypes in SQL tables?
  2. They could be represented as "(3,1)" (in case of point on a plane) VARCHAR and then retrieved by the application and processes them?
  3. What is the difference between Geometrical and Geographical datatypes?
  4. Please mention applications of SDI types with SQL source code for better understanding.
  5. 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
    2. You could store absolutely everything in binary and interpret the 1s and 0s in the application, but the whole point of a shared service like a DBMS is to extract common functionality from applications into a reusable code base. – Michael Green Jun 14 '16 at 1:58
  • I agree with what you say. My point was are these options as widely used to warrant extracting a common functionality? The only reason I wondered this was while learning SQL I was never taught about SDI or their applications. Routing decision making and representation of spatial data are common CS. Applied Mathematics and Geometry problems, but never are we taught PostgreSQL as tools to solve them. Hence the question (which was basically asking for more clarification instead of making assumptions). – ksinkar Jun 14 '16 at 4:08
up vote 5 down vote accepted

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

4) Example:

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.