QueriesGeograficasNoPostGIS Page History

Como funciona

A especificação do OpenGIS define dois padrões para expressarmos os dados espaciais: O Well-Known Text (WKT) e o Well-Known Binary (WKB). O primeiro representa as entidades geográficas na forma de texto e o segundo na forma binária. Ambos os formatos representam o tipo de geometria e as coordenadas da mesma.

Exemplos de tipos de geometrias

POINT(0 0)
LINESTRING(0 0,1 1,1 2)
POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))
MULTIPOINT(0 0,1 2)
MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))
MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))
GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4)))

Sistema de coordenadas

A especificação do OpenGIS também define o sistema de referência cartográfica SRID (Spatial Referencing System Identifier). O SRID é necessário quando você criar objetos geográficos para serem inseridos no banco de dados geográfico.

Para cadastrar ou ler estes formatos, existem as seguintes interfaces:

bytea WKB = asBinary(geometry); 
text WKT = asText(geometry); 
geometry = GeomFromWKB(bytea WKB, SRID); 
geometry = GeometryFromText(text WKT, SRID);

Um exemplo para inserir o objeto geográfico no banco de dados é mostrado a seguir:

INSERT INTO tabelaGeografica ( geom, nomeLocal )
  VALUES ( GeomFromText('POINT(-23.15, -45.79)', 4326), 'NEXUS Parque Tecnológico');

SRID

Para acessar material com conceitos de geodésia, seleciona aqui.

Como obter a projeção cartográfica de um objeto geográfico

Para obter a projeção rode a querie semelhante a apresentada a seguir:

select gid, ST_Srid(geom), ST_AsEWKT(geom) from poligonos

ela irá retornar algo do tipo:

9995;4674;"SRID=4674;POLYGON((340603.35 7385236.99,340620.87 7385238.61,340733.52 7385243.52,340603.35 7385236.99))"
9994;4674;"SRID=4674;POLYGON((340603.35 7385236.99,340620.87 7385238.61,340733.52 7385243.52,340603.35 7385236.99))"

Mudança da projeção cartográfica

Para mudar a projeção cartográfica de uma coluna que contenha uma geometria é utilizada a função:

UpdateGeometrySRID('nome da tabela','nome da coluna que contem a geometria', número da projeção cartográfica)

Um exemplo que altera todas as projeções de todas as linhas de uma tabela é mostrado a seguir:

select UpdateGeometrySRID('poligonos','geom',4674)

Códigos mais utilizados no Brasil

Este tópico muito para, selecione aqui

Obter o texto de uma geometria

Para obter o texto conhecido a partir de uma geometria do banco de dados utilize no SELECT a função ST_AsEWKT ou ST_AsText, como o exemplo abaixo:

ST_AsEWKT

Retorna no formato texto a projeção e a geometria.

SELECT Id, ST_AsEWKT(geom) FROM poligonos

que irá retornar por exemplo:

1;"SRID=4674;POLYGON((340603.35 7385236.99,340620.87 7385238.61,340648.67 7385239.88,340676.47 7385240.97,340704.45 7385242.06,340733.52 7385243.52,340734.61 7385229.32,340735.7 7385209.11,340736.24 7385190.36,340737.33 7385170.88,340737.88 7385151.58,340736. (...)"

ST_AsText

Retorna no formato texto a geometria.

select gid, ST_AsText(geom) from poligonos

que retornará:

1;"POLYGON((340603.35 7385236.99,340620.87 7385238.61,340648.67 7385239.88,340676.47 7385240.97,340704.45 7385242.06,340733.52 7385243.52,340734.61 7385229.32,340735.7 7385209.11,340736.24 7385190.36,340737.33 7385170.88,340737.88 7385151.58,340736.6 7385150. (...)"

Caso rode a querie acima com:

SELECT Id, geom FROM poligonos

o retorno será:

1, "0103000000010000001C00000067666666EDC91441F6285C3F252C5C41AE47E17A33CA1441713D0AA7252C5C41E17A14AEA2CA144185EB51F8252C5C4115AE47E111CB1441E17A143E262C5C41CDCCCCCC81CB14413E0AD783262C5C4148E17A14F6CB144115AE47E1262C5C410AD7A370FACB144148E17A54232C5C41CDCCCCCCFECB1441713D0A471E2C5C415C8FC2F500CC1441713D0A97192C5C411F85EB5105CC144185EB51B8142C5C4152B81E8507CC144152B81EE50F2C5C416766666602CC1441F6285C9F0F2C5C415C8FC2F5B8CB144185EB51880F2C5C41F6285C8F47CB1441E17A144E0F2C5C411F85EB51D8CA14413E0AD7130F2C5C413E0AD7A367CA1441E17A14CE0E2C5C419A999999FAC9144185EB51880E2C5C417B14AE479EC914419A9999590E2C5C41676666669BC914413E0AD7930E2C5C413E0AD7A39AC914411F85EBF1102C5C4185EB51B897C9144185EB51E8152C5C41D7A3703D96C91441EC51B8DE1A2C5C411F85EB5193C914410AD7A3E01F2C5C41713D0AD791C9144152B81E85242C5C41A4703D0A94C91441AE47E1CA242C5C41F6285C8F9AC9144152B81E05252C5C41CDCCCCCCA9C914410AD7A310252C5C4167666666EDC91441F6285C3F252C5C41"

ST_X() e ST_Y()

Retorna os valores x (longitude) e y (latitude) de um ponto geográfico.

Exemplo:

ST_X(ST_Transform(the_geom,4326))

Retorna o valor x de uma geometria do tipo ponto e converte de UTM para WGS-84.

Referência

Retorno de x e y de uma geometria

Como inserir uma nova geometria

INSERT INTO poligonos (gid, geom) VALUES (9995, ST_GeomFromText('POLYGON((340603.35 7385236.99,340620.87 7385238.61,340733.52 7385243.52,340603.35 7385236.99))',4674))

O polígono precisa ter no mínimo 4 coordenadas. Veja que as coordenadas são separadas por vírgula. O sistema de projeção é o GCS SIRGAS 2000, 4674.

Veja que depois de POLYGON existem dois parenteses, isto é por quem cada parentese representa um polígono.

Definição do SRID NO Postgres

ALTER TABLE nome_da_tabela ALTER COLUMN coluna_espacial TYPE Geometry(tipo_da_geometria, numero_do_SRID_desejado) USING ST_SetSRID(coluna_espacial, numero_do_SRID_desejado);

Exemplo:

ALTER TABLE ponto_onibus ALTER COLUMN geom TYPE Geometry(Point, 31982) USING ST_SetSRID(geom,31982);

(define o SRID 31982 – SIRGAS 2000 UTM 22 Sul – para a tabela ponto_onibus, representada por pontos) .

Para alterar um SRID já existente

ALTER TABLE nome_da_tabela COLUMN coluna_espacial TYPE Geometry(tipo_da_geometria, numero_do_SRID_desejado) USING ST_Transform(coluna_espacial, numero_do_SRID_desejado);

Exemplo:

ALTER TABLE dutos ALTER COLUMN geom TYPE Geometry(LineString,29192) USING ST_Transform(geom,29192);

(altera a tabela dutos, representada por linhas, para o SRID 29192 – SAD69 UTM 22 Sul) .

Observações:

As informações sobre todos os sistemas de coordenadas disponíveis encontram-se na tabela spatial_ref_sys, no schema public.
As informações sobre os SRIDs das tabelas encontram-se na view geometry_columns, no schema public.

Fonte

Conversão de uma projeção cartográfica para outra

Crie uma nova coluna geométrica para o dado na nova projeção

alter table nome_da_tabela add column nome_da_nova coluna geometry(tipo_de_geometria,numero_da_projeção)

por exemplo:

alter table gs_consumidores add column geom2 geometry(Point,4674)

Atualize a nova coluna

update gs_consumidores set geom2=ST_Transform(geom,4674)

Selecione para ver o resultado

select ST_AsText(geom2) from gs_consumidores

Caso deseje testar a transformação antes de atualizar a coluna

select ST_AsText(ST_Transform(geom,4674)) from gs_consumidores limit 1

Exemplo de aplicação para inserir uma linestring

Aplicação desenvolvida em C# 2017 versão community

Tabela criada

-- Table: public.cadastro2

-- DROP TABLE public.cadastro2;

CREATE TABLE public.cadastro2
(
    id bigint NOT NULL,
    nome character varying COLLATE pg_catalog."default",
    rede geometry,
    CONSTRAINT cadastro2_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.cadastro2
    OWNER to postgres;

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace teste3
{
    static class Program
    {
        /// <summary>
        /// Ponto de entrada principal para o aplicativo.
        /// </summary>
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Npgsql;

namespace teste3
{
    public partial class Form1 : Form
    {
        private DataSet ds = new DataSet();
        private DataTable dt = new DataTable();
        public Form1()
        {
            InitializeComponent();
            inicializa();
        }
        private void inicializa()
        {
            try
            {
                // PostgeSQL-style connection string
                string connstring = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=Nexus243;Database=geosan";
                // Making connection with Npgsql provider
                NpgsqlConnection conn = new NpgsqlConnection(connstring);
                conn.Open();
                // Start mounting string to insert
                string SQL = "INSERT INTO cadastro2 (id, nome, rede) VALUES (6,'Mônica', ST_GeomFromText('LINESTRING(10 20,40 50)',4674))";
                // Execute command
                NpgsqlCommand command = new NpgsqlCommand(SQL, conn);
                Int32 rowsaffected = command.ExecuteNonQuery();
                // quite complex sql statement
                string sql = "SELECT id, nome, ST_AsText(rede) FROM cadastro2";
                // data adapter making request from our connection
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                // i always reset DataSet before i do
                // something with it.... i don't know why :-)
                ds.Reset();
                // filling DataSet with result from NpgsqlDataAdapter
                da.Fill(ds);
                // since it C# DataSet can handle multiple tables, we will select first
                dt = ds.Tables[0];
                // connect grid to DataTable
                dataGridView1.DataSource = dt;
                // since we only showing the result we don't need connection anymore
                conn.Close();
            }
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                MessageBox.Show(msg.ToString());
                throw;
            }
        }
    }
}