Reading and writing geographic objects

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
[email protected]
Posts: 27
Joined: Fri 12 Feb 2010 07:44

Reading and writing geographic objects

Post by [email protected] » Wed 11 May 2011 14:29

Hello.

Could you give me some example how can I read and write data from/to geometry type?

I work with ODAC and TOraObject type on Oracle database and everything works fine.
Now I'm trying to use PostgreSQL (with PostGIS) and I can't find any examples for that. I suppose I should use TPgGeometric type but I have no idea how.
Can you help me.

Regards

Wojto

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Thu 12 May 2011 07:39

Hello,

PgDAC has several types to work with geometric objects: TPgGeometric, TPgPoint, TPgPointsArray, TPgPolygon, TPgCircle, TPgBox, TPgPath, TPgLSeg, TPgPath. For more information, please see the PgObjects Unit Members topic in the PgDAC help.

Below is a small sample demonstrating how to use geometric objects in PgDAC:

Code: Select all

CREATE TABLE test_geom
(
  f_point point,
  f_id integer NOT NULL
);

INSERT INTO test_geom(
            f_point, f_id)
    VALUES (point(2,1), 1);

Code: Select all

PgQuery1.SQL.Text := 'SELECT * FROM test_geom';
PgQuery1.Open;
ShowMessage(FloatToStr(TPgPoint(TPgGeometricField(PgQuery1.fieldByName('f_point')).AsPgGeometric).X));
ShowMessage(FloatToStr(TPgPoint(TPgGeometricField(PgQuery1.fieldByName('f_point')).AsPgGeometric).Y));

[email protected]
Posts: 27
Joined: Fri 12 Feb 2010 07:44

Post by [email protected] » Thu 12 May 2011 12:02

Thank you for that example.
That specyfic sytuation with "POINT" type works fine.
I work with BDS2006. Your example for reading point:

TPgGeometric *geom = ((TPgGeometricField*) qSelect->FieldByName("f_point"))->AsPgGeometric;

TPgPoint *p1 = (TPgPoint*) geom;
p1->X;
p1->Y;

Everyting is OK. But it's simplest case. I have problems with other types. I try read line and polygon from GEOMETRY type:

"the_geom" is "GEOMETRY" type field.

TPgGeometric *geom = ((TPgGeometricField*) qSelect->FieldByName("the_geom"))->AsPgGeometric;

TPgPolygon *poly = (TPgPolygon*) geom;

for ( int i = 0; i Count; i++ )
{
TPgPoint *p1 = poly->Points;
}

It doesn't work.
poly->Count returns unrealistic huge value and "Points" property also wrong values.

Could you give me another example for reading polygon? I will be grateful.

Regards
Wojto

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Thu 12 May 2011 13:05

Hello,

Please try to execute the following code:

Code: Select all

CREATE TABLE test_geom
(
  f_id integer NOT NULL,
  f_polygon polygon
)

INSERT INTO test_geom(f_id, f_polygon)
    VALUES ( 1, '((1,2),(3,4),(5,6))'::polygon);

Code: Select all

	PgQuery1->Open();
	if (!PgQuery1->FieldByName("f_polygon")->IsNull)
	{
		TPgPolygon *poly = (TPgPolygon*)((TPgGeometricField*) PgQuery1->FieldByName("f_polygon"))->AsPgGeometric;

		for ( int i = 0; i Count; i++ )
		{
			ShowMessage(FloatToStr(poly->Points->X));
			ShowMessage(FloatToStr(poly->Points->Y));
		}
	}
it works correctly.

[email protected]
Posts: 27
Joined: Fri 12 Feb 2010 07:44

Post by [email protected] » Thu 12 May 2011 14:41

Yes, that code works too, but field in table is "POLYGON" type. It's limitation for me, because in one database column I can hold only one type of geometry.
Is it possible to store geometry in "GEOMETRY" type and read and write objects to that type through PgDAC?

In ODAC I can read object as a general geometry type and then get type of object (point, line etc) and read coordinates.

Could you give an example for adding e.g polygon to database?

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Fri 13 May 2011 10:57

Hello,

You can save a polygon object to a database in the following way:

Code: Select all

TPgGeometricField(PgQuery1.FieldByName('f_poligon')).AsString := '((0,0),(0,1),(0,2))';
We will investigate the possibility of adding ability to work with geometric objects directly in one of the next versions of PgDAC.

[email protected]
Posts: 27
Joined: Fri 12 Feb 2010 07:44

Post by [email protected] » Mon 16 May 2011 10:03

Thank you for your answer.
I have three more questions:

1. Is that mean that PgDAC works only with native fields types for PostreSQL and doesn't with types "GEOMETRY" and "GEOGRAPHY" from PostGIS?

2.I'v installed and tested UniDAC also.
I'v noticed that TUniQuery doesn't contains properties which I'v used in TOraQuery for reading and writing geometry, e.g. ->GetObject(). Can I not use UniDAC instead of ODAC?

3. Is it possible to install PgDAC and ODAC simultaneously? I am asking because of problem from second question.

Regards
Wojto

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Mon 16 May 2011 12:55

Hello,

1) Yes, PgDAC works only with native field types for PostreSQL.
2) You can't work with Oracle Objects in UniDAC, because UniDAC is a universal product, and we can't implement all specific features for all databases, because some specific features from one database are not compatible with other databases.
3) Product compatibility is provided for the current build only. To make ODAC and PgDAC work simultaneously on the same IDE, you should use the latest versions of them. The latest compatible versions are the following: ODAC 7.20.0.6, PgDAC 2.20.0.6.

[email protected]
Posts: 27
Joined: Fri 12 Feb 2010 07:44

Post by [email protected] » Mon 16 May 2011 13:38

Ok. Thank you for that informations.

Regards
Wojto

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Post by AlexP » Mon 16 May 2011 13:49

Hello,

If any other questions come up, please contact us.

thvedel
Posts: 14
Joined: Wed 04 May 2005 06:39
Location: Denmark

Re: Reading and writing geographic objects

Post by thvedel » Wed 17 Apr 2013 07:43

I'm a very happy user of UniDAC, but now I need to handle and manipulate objects of GEOMETRY type in a PostgreSQL database, which is not possible using UniDAC.
AlexP wrote:We will investigate the possibility of adding ability to work with geometric objects directly in one of the next versions of PgDAC.
Is this feature added in the current version?

Regards,
Thomas

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Reading and writing geographic objects

Post by AlexP » Thu 18 Apr 2013 13:54

Hello,

The GEOMETRY type is a PostgreSQL server-specific type and it cannot be brought to UniDAC. Therefore in UniDAC you can work with such field types only as with strings. For example, to record data to a Point field, you can use the following code:

Code: Select all

  UniQuery1.FieldByName('f_point').AsString := '(2,3)';

thvedel
Posts: 14
Joined: Wed 04 May 2005 06:39
Location: Denmark

Re: Reading and writing geographic objects

Post by thvedel » Fri 19 Apr 2013 11:20

I know that the feature is not available in UniDAC. My question is if the feature is now available in PgDAC?
AlexP wrote:We will investigate the possibility of adding ability to work with geometric objects directly in one of the next versions of PgDAC.
Is this feature added in the current version?

Regards,
Thomas

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Reading and writing geographic objects

Post by AlexP » Sat 27 Apr 2013 11:16

Hello,

This feature is added to PgDAC, it will be available in the next build.

thvedel
Posts: 14
Joined: Wed 04 May 2005 06:39
Location: Denmark

Re: Reading and writing geographic objects

Post by thvedel » Mon 03 Jun 2013 14:08

How do I get access to the current version of PgDAC?

I have a current subscription (bought on 22 April 2013), but I am only able to download 3.x versions?

Best regards
Thomas

Post Reply