Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
+1 just for using Paint on a Sunday morning –  Yaroslav Feb 23 at 10:01
1  
Could you tell what do you want to achieve, what is not working? –  MiNT Feb 23 at 12:25
    
To update all car_check table field fencing,the picture is my first sql –  Alex Yu Feb 23 at 14:11
    
Do you get an error or something? –  dezso Feb 23 at 21:07
    
The code is correct.But it just can write the first row on fencing field.I want to do the code which can write the all rows –  Alex Yu Feb 24 at 4:49

2 Answers 2

up vote 3 down vote accepted

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 +)

share|improve this answer
    
Wow,it's very helpful to me. –  Alex Yu Mar 8 at 14:57
    
t.log::float8, t.lat::float8 have to upside down –  Alex Yu Mar 8 at 14:58
    
@AlexYu: Thanks, fixed. –  Erwin Brandstetter Mar 8 at 23:16

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)

share|improve this answer
    
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 –  Alex Yu Feb 24 at 5:49
    
where carid='AD-5487' and cargroupid='1' , I want it to update to all rows. –  Alex Yu Feb 24 at 5: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 at 5: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 at 5:53
    
try this one. BTW, it will help if you could provide a SQL Fiddle with a sample data. –  cha Feb 24 at 6:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.