PostgreSQL Data Types - I
The data type defines what type of data can contain a column. PostgreSQL database is very rich in native data types. It supports all data types specified by SQL and you can add new type using CREATE TYPE command. PostgreSQL support data types to store geometric data, XML data and network address data. We can use arrays, hstore and JSON data types to make things more dynamics.
In this post we are going to look at inet and point data types in PostgreSQL.
INET Data Type:
The INET type is used when you want to store IPv4 or IPv6 addresses. It can also store networks by specifying a netmask. If you try to store something that isn't a valid IP address or network, PostgreSQL will throw an error, so you're basically getting data validation for free. There are a variety of methods you can use in your SQL queries to interrogate IP and network addresses further.
postgres=# CREATE TABLE addrs (address INET);
CREATE TABLE
postgres=# INSERT INTO addrs VALUES ('127.0.0.1');
INSERT 0 1
postgres=# INSERT INTO addrs VALUES ('hello world');
2019-05-27 14:20:53.148 IST [1107] ERROR: invalid input syntax for type inet: "hello world" at character 27
2019-05-27 14:20:53.148 IST [1107] STATEMENT: INSERT INTO addrs VALUES ('hello world');
ERROR: invalid input syntax for type inet: "hello world"
LINE 1: INSERT INTO addrs VALUES ('hello world');
^
postgres=# INSERT INTO addrs VALUES ('202.121.0.0/16');
INSERT 0 1
postgres=# SELECT address, host(address), broadcast(address), netmask(address) FROM addrs;
address | host | broadcast | netmask
----------------+-------------+--------------------+-----------------
127.0.0.1 | 127.0.0.1 | 127.0.0.1 | 255.255.255.255
202.121.0.0/16 | 202.121.0.0 | 202.121.255.255/16 | 255.255.0.0
POINT Data Type
The POINT data type is fundamental building block geometric data types. Values point type can be specified in either ways.
( x , y )
x , y
where x and y can be co ordinates or longitude and latitude. Points are taken as (longitude, latitude) and not vice versa because longitude is closer to the intuitive idea of x-axis and latitude to y-axis.
postgres=# CREATE TABLE location ( id serial PRIMARY KEY, name varchar(50) NOT NULL, loc point );
CREATE TABLE
postgres=# INSERT INTO location(name, loc) values ('SupplyHog HQ' , POINT(-85.3071590,35.0472780));
INSERT 0 1
postgres=# INSERT INTO location(name, loc) values ('Chickamauga Dam' , POINT(-85.2197027,35.0975557));
INSERT 0 1
postgres=# INSERT INTO location(name, loc) values ('Five Points Mtn Biking' , POINT(-85.423983,34.851249));
INSERT 0 1
postgres=# INSERT INTO location(name, loc) values ('Harrison Bay State Park' , POINT(-85.114359,35.179631));
INSERT 0 1
postgres=# INSERT INTO location(name, loc) values ('Mojo Burrito' , POINT(-85.3275640,35.0094040));
INSERT 0 1
postgres=# INSERT INTO location(name, loc) values ('Rock Creek' , POINT(-85.2803290,35.0556150));
INSERT 0 1
Using point you can find locations which are less than 10 miles of our current location. Lets say you have current location of:
Then using below SQL query you can find locations, for this you have to create cube and earthdistance extension in database.
postgres=# select *, (point(-85.3078294, 35.0609500) <@> loc) as distance from location where (point(-85.3078294, 35.0609500) <@> loc) < 10 order by distance;
id | name | loc | distance
----+-----------------+--------------------------+-------------------
1 | SupplyHog HQ | (-85.307159,35.047278) | 0.945402695109234
6 | Rock Creek | (-85.280329,35.055615) | 1.59843635194429
5 | Mojo Burrito | (-85.327564,35.009404) | 3.73237070026252
2 | Chickamauga Dam | (-85.2197027,35.0975557) | 5.58807633618004
References:
Comments
Post a Comment