Search code examples
postgresqlsubquerypostgis

How to use subquery as a geometry parameter on postgis?

I need to compare some spatial data on postgis, but I need to compare with not geometry data in another table "card_history", witch contains lat and long fields necessary to use "ST_GeomFromText" function. But how do I do it in a single query? Since I'm working on php (witch is not relevant) it would be very painful to make a lot of queries...

I'm trying something like this:

SELECT ST_Distance_Sphere
  (ST_GeomFromText('POINT(
      (SELECT DISTINCT ON (userid) long from card_history) 
      (SELECT DISTINCT ON (userid) lat from card_history))',4326),
   ST_GeomFromText('POINT(17.0712215 48.15825)',4326)
  )

But the subquery doesn't seem to work as parameter for "POINT". Please Help

Solution

  • try this

    WITH 
lon AS (
    SELECT DISTINCT ON (userid) AS long from card_history
), 

 lat AS (
     SELECT DISTINCT ON (userid) AS lat from card_history
)

SELECT ST_Distance_Sphere(ST_GeomFromText(

'POINT(' || lon.lon || ' ' || lat.lat || ',4326'),

ST_GeomFromText('POINT(17.0712215 48.15825)',4326))

FROM lon, lat;