2

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions?

The code is the first carid example.

UPDATE car_check 
set fencing=ST_Contains
from (
SELECT ST_Contains(ST_AsText('01030000..'),
'POINT(23.912784 120.99178)') 
)as foo
where carid='AD-5487' and cargroupid='1'

enter image description here

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Alan Yu
  • 145
  • 7

2 Answers2

3

Try this untangled version to update all rows of char_check:

UPDATE car_check c
SET    fencing = ST_Contains(u.geom, ST_MakePoint(t.lat::float8, t.lng::float8))
FROM   test t
      ,rule r
JOIN   (
   SELECT DISTINCT ON (polyname)
          polyname, geom
   FROM   unknown_table
   ORDER  BY polyname, version DESC
    ) u USING (polyname)
WHERE  t.id = c.carid
AND    r.cargroupid = c.cargroupid

Major points

  • Use the simpler ST_MakePoint to construct a point geometry from numeric input.

  • You do not need ST_AsText() at all, since your column geom is of type geometry already.

  • Fastest and simplest way to get the geometry for the biggest version per polyname in table unknown_table is to use DISTINCT ON, which is a Postgres extension of the SQL standard. More details in this related answer:
    How do I efficiently get "the most recent corresponding row"?

  • In reference to the currently accepted but invalid answer: the concatenation operator in Postgres (and standard SQL) is || (not +)

Community
  • 1
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
2

You don't seem to join the tables you are trying to update from. I think the query will need to be converted to this:

UPDATE car_check 
set fencing=foo.ST_Contains
from (
SELECT ST_Contains(ST_AsText(p.geom),
ST_GeomFromText('POINT(' || to_char(t.lat, '9999.999999') || ' ' || to_char(t.log, '9999.999999') || ')') AS ST_Contains, cc.carid, cc.cargroupid
FROM test t INNER JOIN car_check cc ON t.id = cc.carid
INNER JOIN rule r ON cc.cargroupid = r.cargroupid
INNER JOIN poly p ON r.polyname = p.polyname
WHERE p.poly_version = (SELECT MAX(p1.poly_version) FROM poly p1 WHERE p1.polyname = p.polyname))as foo 
INNER JOIN car_check 
ON foo.carid = car_check.carid AND foo.cargroupid = car_check.cargroupid
where carid='AD-5487' and cargroupid='1'

(I have no idea how your last table is called. The name is cut off from the picture. I called it poly in my statement. Fix it in yours)

cha
  • 1,055
  • 5
  • 7
  • parse error - invalid geometry LINE 5: 'POINT(t.lat, t.log)') AS ST_Contains, cc.carid, cc.cargroup... ^ HINT: "POINT(t." <-- parse error at position 8 within geometry – Alan Yu Feb 24 '14 at 05:49
  • where carid='AD-5487' and cargroupid='1' , I want it to update to all rows. – Alan Yu Feb 24 '14 at 05:51
  • see my updated answer. For some reason I left the single quotes from your original query. They do not need to be there – cha Feb 24 '14 at 05:51
  • go ahead and remove the last where clause. I left it there, as I thought you are interested in those records only – cha Feb 24 '14 at 05:53
  • try this one. BTW, it will help if you could provide a SQL Fiddle with a sample data. – cha Feb 24 '14 at 06:23
  • +1 for the effort of translating paint to sql. :) But @AlexYu: why is this answer accepted? The syntax is invalid. – Erwin Brandstetter Feb 24 '14 at 14:34
  • @ErwinBrandstetter: thanks for pointing out about the string concatenation. got mixed it with SQL Server – cha Feb 24 '14 at 21:20